Discussion Locked

How NULLs affect IN and EXISTS

0 helpful
2 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

This week's Oracle e-newsletter explains how NULLs affect IN and EXISTS.

After reading this tip, do you understand how NULLs affect IN and EXISTS? What topic would you like us to cover in future editions of this e-newsletter? Please let us know.

If you aren't subscribed to the free Oracle e-newsletter, click the following link to automatically sign up:
http://nl.com.com/MiniFormHandler?brand=builder&subs_channel=bldr_front_door&list_id=e050&tag=fb

2 replies

by gerg

A minor note. In ORACLE 8i

select 'true' from dual where (1,2) not in ((2,3),(2,null));

will return a row because IN evaluation stops with FALSE at 1=2 or 1=2 . So ORACLE sees no need to compare 2:null. But

select 'true' from dual where (1,2) not in ((2,3),(1,null));

will not return any row because (1=2 or 1=1 and 2=null) evaluates to NULL.

by swstephe

The same query you mentioned give the same results in Oracle 9i, so I think it is standard SQL. The comparison of (1,2) and (2,null) is false because the first term evaluates to false, the comparison of (1,2) and (1,null) is null because the first term matches and the second term evaluates the whole expression to null.

Your expressions are equivalent to:

select 'true' from dual where not (1=2 and 2=3) and not (1=2 and 2=null);

... which returns a row and ...

select 'true' from dual where not (1=2 and 2=3) and not (1=1 and 2=null)

which does not return a row.

This discussion is closed to new replies.