General discussion

Locked

How NULLs affect IN and EXISTS

By MaryWeilage Editor ·
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

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Evaluation can stop before all specified conditions are checked.

by gerg In reply to How NULLs affect IN and E ...

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.

Collapse -

Still the case in 9i, maybe it is expected behaviour?

by swstephe In reply to Evaluation can stop befor ...

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.

Back to Software Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums