IN and NOT IN operators with NULL value

When we use "IN" and "NOT IN" operator, we need to be very carefull if the subquery contains NULL value. The record set returnd "IN" plus "NOT IN" does not equal the record set without the condition. Let’s first take look if there is no NULL values in the subquery:
CREATE TABLE #t1 (c1 int not null)
CREATE TABLE #t2 (c1 int not null)
GO

INSERT #t1 (C1) VALUES (1)
INSERT #t1 (C1) VALUES (3)
INSERT #t1 (C1) VALUES (5)
GO
INSERT #t2 (C1) VALUES (1)
INSERT #t2 (C1) VALUES (2)
INSERT #t2 (C1) VALUES (4)
GO

SELECT * FROM #t1 where c1 in (SELECT c1 FROM #t2)
— Returned one record (c1=1)
SELECT * FROM #t1 where c1 not in (SELECT c1 FROM #t2)
— Returned two records (c1=3,5)
So the union of the two queriy results is the same with the query without any conditions

If the subquery does not have any records, the above conclusion is still correct. Try the following queries:
SELECT * FROM #t1 where c1 in (SELECT c1 FROM #t2 WHERE c1>7)
— Returned 0 records
SELECT * FROM #t1 where c1 not in (SELECT c1 FROM #t2 WHERE c1>7)
— Returned all 3 records

Now lets modify table #t2 to allow c1 being NULL and insert a record with c1=NULL
ALTER TABLE #t2 ALTER COLUMN c1 int null
GO
INSERT #t2 (C1) VALUES (NULL)
GO
Run the following queries:
SET ANSI_NULLS ON
SELECT * FROM #t1 where c1 in (SELECT c1 FROM #t2)
SELECT * FROM #t1 where c1 not in (SELECT c1 FROM #t2)
SELECT * FROM #t1 where c1 not in (SELECT c1 FROM #t2 WHERE c1 IS NOT NULL)
The first one still returns one record (c1=1). However the second query returns nothing. Because a record with NULL value is in the subquery and NULL is a unknown value and can be any value. So "NOT IN" eliminates all records from the main query. But the "IN" opertaor does not inlcude all records from the main query. The union of the two queriy results is the same with the query without any conditions.

The following queries behavour the same as the example when c1 in #t2 is not nullable.
SELECT * FROM #t1 where c1 in (SELECT c1 FROM #t2 WHERE c1>7)
SELECT * FROM #t1 where c1 not in (SELECT c1 FROM #t2 WHERE c1>7)

If we turn off ANSI_NULLS setting, the result will be different.
SET ANSI_NULLS OFF
SELECT * FROM #t1 where c1 in (SELECT c1 FROM #t2)
— Returned one record (c1=1)
SELECT * FROM #t1 where c1 not in (SELECT c1 FROM #t2)
— Returned one record (c1=3,5)
SELECT * FROM #t1 where c1 in (SELECT c1 FROM #t2 WHERE c1>7)
SELECT * FROM #t1 where c1 not in (SELECT c1 FROM #t2 WHERE c1>7)
The result is the same as when c1 in #t2 is not nullable becuase NULL is treated as value not a unknown value. We can see this more clearly by the following examples:

ALTER TABLE #t1 ALTER COLUMN c1 int null
GO
INSERT #t1 (C1) VALUES (NULL)
GO
SET ANSI_NULLS ON
SELECT * FROM #t1 where c1 in (SELECT c1 FROM #t2)
— Returned one record (c1=1)
SELECT * FROM #t1 where c1 not in (SELECT c1 FROM #t2)
— Returned nothing
SELECT * FROM #t1 where c1 not in (SELECT c1 FROM #t2 WHERE c1 IS NOT NULL)
— Returned two records (c1=3,5)
SELECT * FROM #t1 where c1 in (SELECT c1 FROM #t2 WHERE c1>7)
— Returned nothing
SELECT * FROM #t1 where c1 not in (SELECT c1 FROM #t2 WHERE c1>7)
— Returned all records (c1=1,3,5,NULL)
GO
SET ANSI_NULLS OFF
SELECT * FROM #t1 where c1 in (SELECT c1 FROM #t2)
— Returned two records (c1=1,NULL)
SELECT * FROM #t1 where c1 not in (SELECT c1 FROM #t2)
— Returned one record (c1=3,5)
SELECT * FROM #t1 where c1 not in (SELECT c1 FROM #t2 WHERE c1 IS NOT NULL)
— Returned three record (c1=3,5,NULL)
SELECT * FROM #t1 where c1 in (SELECT c1 FROM #t2 WHERE c1>7)
— Returned nothing
SELECT * FROM #t1 where c1 not in (SELECT c1 FROM #t2 WHERE c1>7)
— Returned all records (c1=1,3,5,NULL)

Advertisements
This entry was posted in SQL server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s