NULL and Three-Valued Logic

The single concept behind the most expensive silent bugs in reporting.

NULL means unknown, not empty

WHERE Bonus = NULL returns nothing, ever — because it asks whether one unknown value equals another unknown value. SQL has three truth values, not two: TRUE, FALSE, and UNKNOWN. WHERE keeps only rows that evaluate to TRUE; UNKNOWN is rejected exactly like FALSE. Use IS NULL or IS NOT NULL instead.

Rule

NOT UNKNOWN is still UNKNOWN. So WHERE NOT (Bonus = 5000) excludes the same NULL rows that WHERE Bonus = 5000 excluded — two queries that look like complements do not partition the table between them.

It propagates through arithmetic and concatenation

SELECT FirstName, Salary + Bonus AS TotalPay FROM Employees;

Every employee with no recorded bonus gets a NULL total, not their salary — unknown plus anything is unknown. Add a filter on that total and those employees vanish from the report with no error at all. Fix the arithmetic deliberately with a default, once you've decided the default is correct:

SELECT FirstName, Salary + COALESCE(Bonus, 0) AS TotalPay FROM Employees;

String concatenation inherits the same propagation, but the operator that carries it is not portable: SQL Server's + nulls the whole string on one missing part; Postgres and Oracle use ||; MySQL's + attempts numeric addition and quietly turns text into 0. CONCAT(...) is the portable choice and treats NULL as empty — which avoids an error but can just as easily hide a missing field. Deciding which failure mode is acceptable for a given report is the actual work; the function alone doesn't decide it for you.

NOT IN with a NULL in the list is silently empty

SELECT * FROM Employees WHERE Department NOT IN ('Finance', NULL);

Zero rows, always — because the engine can never rule out a match against the unknown value in the list. IN with a NULL in the list is harmless; NOT IN is lethal the moment the list can contain one. This is why NOT EXISTS is the safer default for exclusion queries.

Aggregates ignore NULL — except one, and only sometimes

Rule

Every aggregate except COUNT(*) ignores NULL inputs, including when deciding AVG's denominator. And every aggregate except COUNT returns NULL — not zero — over an empty group. Two defensible numbers can differ by a wide margin; knowing which one a report is quoting is the job.

NULL equality is deliberately inconsistent

NULL = NULL is UNKNOWN, but GROUP BY, DISTINCT, UNION and ORDER BY all treat NULLs as equal to each other. All three bonus-less employees land in a single GROUP BY Bonus bucket despite that comparison never being TRUE. This is intentional in the standard, not a bug — and naming the inconsistency accurately beats being surprised by it later. Sort position for NULLs is also engine-dependent (SQL Server sorts them first ascending; Postgres and Oracle sort them last, or accept an explicit NULLS FIRST / NULLS LAST).

A NULL that looks the same can mean three different things

• Doesn't apply — a contractor has no bonus scheme at all.

• Applies but isn't known yet — payroll hasn't filed it.

• Is genuinely zero — someone stored NULL instead of 0 out of habit.

Key takeaway

Don't default NULL to zero just to make an average compute. Decide which of the three cases above applies first — the honest fix is usually upstream in the schema, not a COALESCE bolted onto the report.

Guarding a division with NULLIF

SELECT Budget / NULLIF(Headcount, 0) AS SpendPerHead FROM Departments;

NULLIF(a, b) returns NULL when the two arguments are equal — here, turning a zero headcount into an honest 'undefined' cell instead of a divide-by-zero error that kills the whole query.

Try it

SELECT FirstName, Salary + Bonus AS TotalPay
FROM Employees
ORDER BY TotalPay;