I’ve written about NULLS before, but they still find ways to trip us up. It’s always worth protecting yourself from them in a query as they can drive you mad!

Take a simple ‘in/not in’ subquery:

select A from tableA where
A in (select B from tableB)

Lets assume that there is some matching data; those records will be returned. So far so good. Now reverse it, to get those records that don’t match:

select A from tableA where
A NOT in (select B from tableB)

If there are NULLs in column B of tableB then the query will return no records, even if there are records in there that you know should come back. The solution, as always, is the isnull() function:

select A from tableA where
A not in (select isnull(B, ”) from tableB)

By using this, any NULL values are returned as empty strings instead and the query will work as expected.