Oracle Tip: Understand how NULLs affect IN and EXISTS

August 23, 2004

[From Builder UK]

If your database design allows NULL values in any columns, you'll need to know how different clauses in your queries deal with this .

On the surface, it may appear that the SQL clauses IN and EXISTS are interchangeable. However, they're quite different in how they handle NULL values and may give different results. The problem comes from the fact that, in an Oracle database, a NULL value means unknown, so any comparison or operation against a NULL value is also NULL, and any test that returns NULL is always ignored.

The article continues at http://uk.builder.com/0,39026540,39215369,00.htm