Ever foxed by Nulls in DB (SQL Server and Oracle)?

Recently, I landed into an issue that gave an opportunity to explore more about null values.

Consider two tables X(Int Column A) and Y(Int Column B) with following values:

A (Table X):

-----------

1

NULL

2

3

4

B (Table Y):

-----------

1

2

4

NULL

1. Run the query 'SELECT B FROM Y WHERE B IS NULL'. You will see one NULL record.
2. Run the query 'SELECT * FROM X WHERE A NOT IN(SELECT B FROM Y WHERE B IS NULL ) '. You will see no results. I was expecting it to return 1,2,3, and 4 values.
3. Run the query 'SELECT * FROM X WHERE A IN(SELECT B FROM Y WHERE B IS NULL ) '. You will see no results.
4. Run the query 'SELECT * FROM X WHERE A NOT IN(SELECT B FROM Y WHERE B IS NOT NULL ) '. Again, you will see no results.

Queries listed at 3 and 4 look quite opposite but both return no result. If you are wondering which database I am talking about, I am using SQL Server 2005 with ANSI_NULLS ON. I see the same behavior with Oracle 11i as well.

Reason for this behavior is simple, DBs treat NULLS as 'Unknown' value. If you are trying to compare something with an 'Unknown' value, that is the reason we get an 'Unknown' result.. :) In SQL Server, if you turn off ANSI_NULLS (using SET ANSI_NULLS OFF statement), you would get different results for the above queries.

This leads us to a question. How should we write safe queries to not get surprises while in production?. This article explains how we can use NOT EXISTS instead of NOT IN. You can read more about NULLs in Oracle in this article. And if you are looking for the SQL SERVER help, check out this article.