SQLMentor
SQL NULL Handling: IS NULL vs = NULL, and Where It Bites
NULL means "unknown", and that one fact explains a whole category of SQL bugs — from a WHERE clause that mysteriously excludes rows, to a NOT IN query that returns nothing at all. Here's exactly how NULL behaves, and the tools for handling it safely.
NULL means "unknown", not "empty"
NULL represents the absence of a known value — not zero, not an empty string, and not "blank". Because it means unknown, comparing anything to NULL with a standard operator doesn't return true or false — it returns a third result: UNKNOWN.
This three-valued logic (TRUE / FALSE / UNKNOWN) is the root cause of almost every NULL-related bug in SQL. A WHERE clause only keeps rows where the condition evaluates to TRUE — both FALSE and UNKNOWN are filtered out.
Why col = NULL never works
commission_pct is NULL for many employees in the HR schema.
-- Returns ZERO rows, even for employees whose commission_pct really is NULL
SELECT * FROM employees WHERE commission_pct = NULL;
-- Correct: use IS NULL / IS NOT NULL
SELECT * FROM employees WHERE commission_pct IS NULL;
SELECT * FROM employees WHERE commission_pct IS NOT NULL;Why = NULL is always UNKNOWN
= asks "are these two values the same?" — but NULL isn't a value, it's the absence of one. Comparing an unknown quantity to anything, including another NULL, can't honestly be answered yes or no, so SQL returns UNKNOWN. NULL = NULL is UNKNOWN, not TRUE — even two NULLs are not considered "equal" by the comparison operator.
Where NULL quietly changes behaviour
| Context | What happens |
|---|---|
| Aggregate functions (SUM, AVG, COUNT(col), MAX, MIN) | NULLs are skipped entirely — AVG(salary) divides by the count of non-NULL salaries, not the total row count |
| COUNT(*) vs COUNT(column) | COUNT(*) counts every row; COUNT(column) counts only rows where that column is not NULL |
| Concatenation (Oracle ||) | NULL || 'text' returns 'text' in Oracle (NULL treated as empty string) — but behaves differently in other dialects, so test on your target database |
| NOT IN with a NULL in the list | silently returns zero rows for the whole query — see the IN vs EXISTS article |
| ORDER BY | Oracle and PostgreSQL sort NULLs as the largest value by default (last in ASC, first in DESC); always use NULLS FIRST / NULLS LAST to be explicit if it matters |
| Unique constraints | multiple NULLs are allowed in a UNIQUE column, because NULL is never considered equal to another NULL |
Functions for handling NULL
The standard toolkit for defaulting and branching on NULLs safely.
-- Replace NULL with a default value
SELECT last_name, NVL(commission_pct, 0) AS commission
FROM employees;
-- First non-NULL value across several columns/expressions
SELECT COALESCE(commission_pct, bonus_pct, 0) AS effective_rate
FROM employees;
-- Branch depending on whether a value is NULL
SELECT last_name, NVL2(commission_pct, 'Has commission', 'No commission') AS status
FROM employees;Does NULL behaviour differ across databases?
The core rule — = NULL is always UNKNOWN, aggregates skip NULLs — is standard SQL and identical everywhere. What varies is convenience syntax: default ORDER BY NULL placement differs by dialect, and NULL-safe equality (treating two NULLs as equal instead of unknown) has different spellings — PostgreSQL and SQL Server offer IS [NOT] DISTINCT FROM, MySQL has the <=> operator, and in Oracle the common workaround is DECODE(a, b, 1, 0) = 1 (DECODE treats two NULLs as a match, unlike =).