Re: Null in subquery returns no records

From: ben brugman <ben_at_niethier.nl>
Date: Tue, 10 Feb 2004 10:28:20 +0100
Message-ID: <4028a435$0$1413$4d4ebb8e_at_read.news.nl.uu.net>

"Lennart Jonsson" <lennart_at_kommunicera.umea.se> wrote in message >
> x not in (y1, y2, ..., null) ->
> not (x=y1 or x=y2 or ... x=null) ->
> x!=y1 and x!=y2 and ... x!=null ->
> ... and null ->
> null
>

Three valued logic is problematic, but I do not agree with the above. Not all conditions containing a null evalutate to null, for example

False AND null evaluates to False

your line ... and null -> null does not 'respect' that rule.

For tree valued logic, the extra set above boolean logic is :

1. Not null --> null
2. false and null --> false
3. true and null --> null
4. false or null --> null
5. true or null --> true

If a complete condition evaluates to null in a 'complete' clause it is handled as false, the 'concerning' row is not selected.

I know this is true for MS-SQL-server and for Oracle, I do not know how this is implemented in other databases.

For Oracle (also MS-SQL-server if you create dual with one row). null is generated by (null = null)
false and true are generated bij a simple compare.

0. select * from dual where (null = null)
1. select * from dual where not (null= null)
2. select * from dual where (1=0) and (null = null)

 use select * from dual where not((1=0) and (null = null)) (Gives a line)  to see that 2 evaluates to false. not false --> true

3. select * from dual where (1=1) and (null = null)
4. select * from dual where (1=0) or (null = null)
5. select * from dual where (1=1) or (null = null)  (Gives a line)

ben brugman

> null is problematic in sql, and there have been lots of discussions
> about it. See for example:
>
> http://www.firstsql.com/iexist2.htm
>
> HTH
> /Lennart
Received on Tue Feb 10 2004 - 10:28:20 CET