NULL is the database saying I do not have a value here — not zero, not an empty string, and not a lie about the world: an unknown city, a manager nobody has hired yet. That absence behaves like no other value in SQL: it fails every comparison, hides from filters, and needs special operators to be seen at all.
What does a NULL actually mean in real data?
The bookshop roster has carried one unidentified city all along — Ruth, the seasonal employee who has yet to be told which branch she will cover. Ask for missing cities with IS NULL, the dedicated predicate:
SELECT name, role FROM staff WHERE city IS NULL;
name role
------------ -------------------
Ruth Adeyemi Seasonal Bookseller
Keep the meaning squared away before moving on. A NULL city says unknown or not yet applicable; it is different from city = '' (an empty string is a real, known name that happens to be length zero) and from 0 in a numeric column (a real count of nothing). Conflating the three is the seed of most NULL-shaped bugs, because downstream code treats zero, empty and unknown as interchangeable when they are not.
Why does = NULL find nothing?
Untrained instinct writes the equality form and gets silence. The engine honoured three-valued logic, not an oversight — anything compared against an unknown is unknown, and WHERE passes only true rows:
SELECT name, role FROM staff WHERE city = NULL;
(no rows)
Not an error, not an exception — silently zero rows, which is precisely why this bug hides in pull requests. The same mechanics can be demonstrated insistently with constants: SELECT 1 WHERE NULL = NULL prints nothing, while IS NULL, being a predicate about emptiness rather than a comparison of unknowns, agrees:
SELECT 1 WHERE NULL IS NULL;
1
How do you find missing and present values on purpose?
IS NULL finds the missing; IS NOT NULL finds the known. Five of the eight staff report to a named manager; the two department heads and the cafe lead — one of whom answers to nobody — fall outside:
SELECT name, manager_id FROM staff WHERE manager_id IS NOT NULL;
name manager_id
------------- ----------
Dana Kowalski 1
Marcus Bell 5
Tom Whitfield 5
Joe Naylor 4
Ruth Adeyemi 5
In production probes, IS NOT NULL is the workhorse: show me the rows where this field was actually filled in. Because ordinary comparisons never see NULLs, the IS family replaces every backdoor trick people try first with != — and stops losing the eighth row to logic instead of oversight.
What do aggregates do with NULLs?
Aggregation functions skip unknowns rather than complaining. COUNT(*) counts rows; COUNT(column) counts the rows where the column holds a value — the same roster, two different facts:
SELECT COUNT(*), COUNT(city), COUNT(manager_id) FROM staff;
COUNT(*) COUNT(city) COUNT(manager_id)
-------- ----------- -----------------
8 7 5
Eight people, seven known cities, five stated managers. The quotient is instantly useful — how much of a column is actually populated — and it is the audit I run when inheriting any table. Data-entry dead columns show up as a COUNT equal to a handful, exactly like the manager_id column here, which quietly means three of eight staff answer to nobody.
Two companion details round out the aggregate behaviour. SUM, AVG, MIN and MAX all skip unknowns, so AVG over a part-missing column is an average of the present values, not an error and not a lower number skewed toward zero; and COUNT(DISTINCT column) counts distinct present values, which matters the moment you want how many different cities rather than rows. Grading a dashboard against an empty-minded read of these two functions is how the eight-vs-seven-vs-five gap above gets mistaken for a data bug when it is simply the truth about unknowns.
Is there a NULL-safe equality, per engine?
Yes, and the spellings form a small dialect gallery. The SQL standard clause IS DISTINCT FROM compares two expressions and answers NULL-safely: unknown equals unknown here, so the emptiness loses its veto. SQLite gained it in 3.39; PostgreSQL has supported it on the standard wording; MySQL reaches the same behaviour through its two-character operator; SQL Server needs a hand-rolled workaround with IS NULL checks on both sides:
-- SQLite runs these (verified on 3.39); PostgreSQL uses the same spelling
SELECT 1 WHERE NULL IS NOT DISTINCT FROM NULL; -- true: both unknown
SELECT 1 WHERE NULL IS DISTINCT FROM NULL; -- false: no row back
-- MySQL equivalent: SELECT 1 WHERE NULL <=> NULL;
My sandbox confirmed both standard variants return the row only on the NOT DISTINCT side, as shown earlier in this lesson. Installation dates matter — older SQLite versions reject IS DISTINCT FROM, and the error text looks nothing like a version mismatch, so pin your engine before writing it into a codebase.
Quick reference
- NULL is absence of a value: not zero, not an empty string, and not false.
- Every comparison involving NULL evaluates to unknown; filters pass only true, so NULL rows drop out of both sides of a filter.
- IS NULL and IS NOT NULL are the only predicates that see unknowns directly.
- Aggregates skip NULLs; COUNT(*) counts rows, COUNT(column) counts present values.
- NULL-safe equality goes by IS DISTINCT FROM (standard, SQLite 3.39+, PostgreSQL) or a MySQL-only operator.
Self-drill: write a three-column audit of the staff table in one query — total headcount, count of known emails, count of stated managers — then two filters: staff with no manager, and staff with a manager but no city. Predict each count before running it, and if any prediction is wrong, say which of the two counts you got right by luck rather than logic.