Wednesday 9 November 2011

NOT IN sub-select query with NULL values

I was wondering why my NOT IN query was failing to return the expected amount of records, so I googled and found the 'SQL with Manoj' post at wordpress. If there are NULL values returned by your sub-select query then you need to handle the NULL values explicitly.

SELECT t.Name ,t.Id
FROM TableOne t
WHERE t.Id NOT IN (SELECT t2.ExternalId FROM TableTwo t2);


This was returning zero results because the result set contained a number of NULL values, making the whole set unknown. You either need to handle the nulls explicitly like this:

SELECT t.Name, t.Id
FROM TableOne t
WHERE t.Id NOT IN
(SELECT t2.ExternalId FROM TableTwo t2 WHERE t2.ExternalId IS NOT NULL);


Or use NOT EXISTS instead:

SELECT t.Name, t.Id
FROM TableOne t
WHERE NOT EXISTS
(SELECT t2.ExternalId FROM TableTwo t2 WHERE t.Id = t2.ExternalId);


Thanks to Manoj for the heads up : )

No comments:

Post a Comment