SQL Fundamentals: Joins, NULL, Aggregate Functions, and Subqueries

Part 5 of 18 · ~1 min

NULL Is Not Zero, Not Empty String, and Not False

Time to deal with Dave directly, because the pay-equity slide going quiet about IT was your first hint, and it won't be the last. Here's the single fact that untangles most of what's coming: a NULL cell isn't secretly a zero, an empty string, or a false wearing a disguise. It's a placeholder for a value the database was never given — think of it less as "the answer is nothing" and more as "the question hasn't been answered yet." Dave's row doesn't say his pay is $0. It says nobody has told the database what his pay is, which is a completely different claim, and treating the two as interchangeable is where most of this chapter's surprises come from.

Suppose you want to flag anyone earning under $52,000 as a candidate for an early review:

SELECT name FROM Employees WHERE salary = 52000;         -- looking for an exact match, fine so far
SELECT name FROM Employees WHERE salary = NULL;           -- returns ZERO rows — every single time, for every row
SELECT name FROM Employees WHERE salary IS NULL;           -- correctly returns Dave
SELECT name FROM Employees WHERE salary IS NOT NULL;        -- everyone except Dave

salary = NULL reads like it should mean "find the row where salary hasn't been set," and it's a genuinely reasonable first guess — it's just wrong. = asks "is this value equal to that value," and there is no honest answer to "is Dave's unknown salary equal to this specific unknown value" other than "unknown." SQL has no way to make = return true for a NULL comparison, on purpose — IS NULL and IS NOT NULL exist as their own dedicated operators specifically because equality can't do this job, not because someone forgot to make it work.