NULL is not zero, and it is not an empty string. It means "unknown" or "missing". Because of that, NULL behaves differently from every other value, and many silent errors in SQL reports come from forgetting it.
Examples use a leads table for a fictional car dealership, AutoWheels: lead_id, name, phone, source, budget, followed_up_on.
Comparisons with NULL are never true
-- Returns no rows, even if some sources are NULL
SELECT * FROM leads WHERE source = NULL;Any comparison with NULL (=, <>, >) gives "unknown", which WHERE treats as false. Use IS NULL and IS NOT NULL:
SELECT * FROM leads WHERE source IS NULL;The <> trap
SELECT * FROM leads WHERE source <> 'Walk-in';This does not return rows where source is NULL. If you want them too:
SELECT * FROM leads WHERE source <> 'Walk-in' OR source IS NULL;Aggregates ignore NULL
COUNT(*)counts all rows.COUNT(budget)counts rows where budget is not NULL.AVG(budget)averages only known budgets.
So if half the leads have no budget, AVG(budget) is the average of the other half. That may be what you want, but say so in your report. If missing should count as zero:
SELECT AVG(COALESCE(budget, 0)) FROM leads;These two give different answers, and choosing between them is a business decision, not a technical one.
COALESCE: first non-NULL value
SELECT name, COALESCE(phone, 'No phone') AS contact FROM leads;
SELECT name, COALESCE(source, 'Unknown') AS source FROM leads;Very useful for display and for grouping, so NULLs appear as a labelled group rather than a blank one.
NULLIF: avoid division by zero
SELECT dealer, sales / NULLIF(leads, 0) AS conversion
FROM dealer_summary;NULLIF(a, b) returns NULL when a = b. Dividing by NULL gives NULL instead of an error.
NULL in arithmetic and text
Any arithmetic with NULL gives NULL: 100 + NULL is NULL. In many databases, concatenating text with NULL also gives NULL. Wrap columns in COALESCE when building labels.
NULL in GROUP BY and ORDER BY
GROUP BY puts all NULLs into one group. ORDER BY places NULLs first or last depending on the database; many support NULLS FIRST / NULLS LAST to control it.
NULL from LEFT JOINs
A LEFT JOIN creates NULLs for unmatched rows. This is useful: WHERE right_table.id IS NULL finds rows without a match. It is also a trap: a WHERE condition on the right table's columns removes those NULL rows and turns the left join into an inner join. Our joins guide shows this in detail.
NOT IN with NULL
SELECT * FROM leads WHERE lead_id NOT IN (SELECT lead_id FROM bookings);If bookings.lead_id contains even one NULL, this returns nothing. Use NOT EXISTS or a LEFT JOIN with IS NULL instead.
A checklist
- Count NULLs in key columns before analysing:
SUM(CASE WHEN col IS NULL THEN 1 ELSE 0 END). - Decide what NULL means for each column (unknown, not applicable, zero).
- Use
IS NULL, never= NULL. - Mention NULL handling in your write-up.
Practise
Our NULL handling set is full of these traps, checked instantly so you see exactly where the counts change.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: