Handling subqueries with NULL

Solution

Make sure you handle the case with IS NOT NULL in the subquery reftut.

Discussion

This is correct - remeber that the only test the NULL passes is IS NULL. So - if you have

SELECT co_name FROM companies WHERE co_id NOT IN (SELECT dy_company FROM diary);
   

You'd be expecting a list of companies without diary entries, and so long as dy_company is never null that's what you'll get. But if there's a null value you'll get no results at all. Why?

We can expand the query as follows:

WHERE co_id NOT IN (SELECT dy_company FROM diary)
WHERE co_id NOT IN (1, 2, null, 3...)
WHERE NOT (co_id=1 OR co_id=2 OR co_id=null OR co_id=3...)
WHERE NOT (null)
WHERE null
   

Remember that Anything <operation> Null returns Null and it starts to make sense.

To solve it either make sure that dy_company is defined as NOT NULL in the table definition (avoid the whole issue) or explicitly check for this case.

SELECT co_name FROM companies WHERE co_id NOT IN (SELECT dy_company FROM diary WHERE dy_company IS NOT NULL);
   

There is more on this in Bruce's book.