SQL Gotcha: Beware the NULL
Today I ran into a little bit of SQL that, on first glance, didn't make a bunch of sense. Before I show you the SQL I'll give you a basic idea of the two tables being queried:
AREA
areaid | int NOT NULL
areaname | varchar2(100) NOT NULL
FACILITY
facilityid | int NOT NULL
facilityname | varchar2(100) NOT NULL
areaid | int NULL
There is actually a bit more to it but these columns are the important ones. The query that had been written was trying to find all areas that had no facilities within them:
SELECT ar.* FROM area ar WHERE ar.areaid NOT IN (SELECT f.areaid FROM facility f)
That query returned no rows at all. Meanwhile the following query returned one row:
SELECT ar.* FROM area ar WHERE NOT EXISTS (SELECT f.areaid FROM facility f.areaid = ar.areaid)
Honestly, I've given you a big hint to figuring this one out already since I titled this thing Beware the Null and I showed you which columns could be NULL and which couldn't. But here is the problem.
Any SQL comparision that involves a NULL will evaluate to FALSE
That means all of the following examples would return no rows:
SELECT * FROM area WHERE areaid NOT IN (1,2,NULL);
SELECT * FROM area WHERE areaid IN (1,2,NULL);
SELECT 1 as myRow FROM dual WHERE 1=null;
SELECT 1 as myRow FROM dual WHERE 1 != null;
SELECT 1 as myRow FROM dual WHERE null = null;
All of those return no rows because any comparison operator used with a null evaluates to false (IN, NOT IN, =, !=, etc).
So, how do I fix the NOT IN query to work the way I expected it to?
SELECT ar.* FROM area ar WHERE ar.areaid NOT IN (SELECT f.areaid FROM facility f WHERE f.areaid IS NOT NULL)
