NULL Handling

NULL causes more silently-wrong SQL than any other feature, and almost every surprise comes from one misunderstanding: NULL does not mean "empty" or "zero". It means "unknown". Once that clicks, every rule below follows from it.

Three-valued logic

Since NULL is unknown, comparing it to anything is also unknown — not true, not false:

NULL = NULL        → UNKNOWN
NULL <> 5          → UNKNOWN
NULL > 0           → UNKNOWN
NULL + 5           → NULL
'abc' || NULL      → NULL       (concatenation, most engines)

And WHERE keeps only rows where the predicate is TRUE — UNKNOWN rows are dropped just like FALSE ones. That is why this returns nothing:

SELECT * FROM employees WHERE commission = NULL;   -- always empty
SELECT * FROM employees WHERE commission IS NULL;  -- correct

The truth tables, which are worth knowing cold:

Note FALSE AND UNKNOWN = FALSE and TRUE OR UNKNOWN = TRUE — the known operand can still decide the result. That is exactly why IN tolerates NULLs while NOT IN does not: a match makes the OR-chain TRUE regardless, but in the negated AND-chain a single UNKNOWN prevents TRUE forever. See filtering for that trap in full.

A NOT filter drops NULL rows

The most common real-world NULL bug, and it looks nothing like a NULL bug:

-- Intent: everyone not in Sales. Employees with NULL department are NOT returned.
SELECT * FROM employees WHERE department <> 'Sales';

-- Fix: say what you mean about the unknowns
SELECT * FROM employees WHERE department <> 'Sales' OR department IS NULL;

-- Or use the NULL-safe comparison
SELECT * FROM employees WHERE department IS DISTINCT FROM 'Sales';   -- Postgres
SELECT * FROM employees WHERE NOT (department <=> 'Sales');          -- MySQL

IS DISTINCT FROM treats NULL as a value that can differ from things — it is the comparison people usually meant to write.

Which functions ignore NULLs

AVG is the one that bites: it divides by the count of non-NULL values.

-- 10 employees, 6 with NULL commission
SELECT AVG(commission)             FROM employees;  -- sum / 4
SELECT AVG(COALESCE(commission,0)) FROM employees;  -- sum / 10

Both are defensible; the interview point is knowing they differ and asking which is wanted.

GROUP BY does the opposite of comparison — it collects all NULLs into one group, because grouping uses "not distinct" rather than =. So does DISTINCT, and so do set operations.

The toolkit

COALESCE(a, b, c)      -- first non-NULL argument; standard, any number of args
NULLIF(a, b)           -- NULL when a = b, else a
IFNULL(a, b)           -- MySQL, two arguments
ISNULL(a, b)           -- SQL Server, two arguments
NVL(a, b)              -- Oracle

Prefer COALESCE — it is standard SQL and works everywhere.

NULLIF has one job that everyone eventually needs, guarding division:

SELECT revenue / NULLIF(order_count, 0) AS avg_order_value FROM daily_stats;
-- zero orders ⇒ NULL, instead of a divide-by-zero error

Sorting

NULL ordering is not standardised. PostgreSQL and Oracle put NULLs last ascending; MySQL and SQL Server put them first. Be explicit when it matters:

ORDER BY commission DESC NULLS LAST;              -- PostgreSQL, Oracle
ORDER BY commission IS NULL, commission DESC;     -- MySQL — the boolean sorts 0 before 1

NULL in joins and constraints

• A NULL join key never matches, including another NULL — rows simply do not join.
• UNIQUE allows multiple NULLs in most engines, because two unknowns are not provably

equal. SQL Server is the exception, allowing only one.

• PRIMARY KEY forbids NULL entirely.
• CHECK constraints pass on UNKNOWN — CHECK (age > 0) does not reject a NULL age.

Add NOT NULL if that is the intent.

Mistakes that cost the interview

• `= NULL` or `<> NULL` instead of IS [NOT] NULL.
• `NOT IN` over a nullable subquery, returning zero rows.
• A `<>` filter that silently excludes NULL rows.
• Assuming `AVG` divides by the row count.
• Assuming NULLs group separately. They form one group.
• Unguarded division where the denominator can be zero or NULL.

Practise these on the SQL sheet.