NULL: the value that isn't there
Part of the free LogicWiz SQL & Databases course, Module 2: Your first queries. The queries and practice run in the app on real Postgres.
Three students have no email. Meher runs WHERE email = NULL to find them and gets zero rows. Then she asks for students "not in Mechanical" and Manish vanishes too. Where did they go?
You'll learn to
- Find missing values with IS NULL and IS NOT NULL
- Predict what NULL does to comparisons, AND, OR and maths
- Replace NULL with a fallback using COALESCE
Learn
NULL means "unknown"
NULL is not zero and not an empty string. It means we don't know.
- Aditya's CGPA is NULL: he is in his first term, so there is no CGPA yet. It is not 0.
- Kabir's email is NULL: he never gave one. It is not
''. - Rohan's EC201 marks are NULL: the result is not out yet.
This matters because 0 is a real number that would drag an average down, while NULL is simply left out.
Who has no email?
SELECT name, email
FROM students
WHERE email IS NULL;
IS NULL is the only way to ask "is this missing?". Its opposite is IS NOT NULL.
Why = NULL finds nothing
SELECT name
FROM students
WHERE email = NULL;
SELECT NULL = NULL AS same, 5 > NULL AS bigger;
Is Kabir's unknown email equal to some other unknown? We can't say. So = NULL gives unknown (shown as NULL) for every row, never true, and WHERE keeps only true rows.
Three-valued logic
SQL conditions have three results: true, false and unknown. Any comparison with NULL is unknown.
WHERE keeps a row only when its condition is true. Unknown is dropped, just like false.
That is why Manish disappears from WHERE dept_id <> 3: is "no department" different from 3? Unknown, so he is dropped, even though he is clearly not in Mechanical. He also disappears from dept_id = 3. He falls through both filters.
AND and OR with unknown
| Expression | Result | Why |
|---|---|---|
| true AND NULL | NULL | Depends on the unknown |
| false AND NULL | false | One false makes AND false |
| true OR NULL | true | One true makes OR true |
| false OR NULL | NULL | Depends on the unknown |
| NOT NULL | NULL | The opposite of unknown is unknown |
| NULL + 5 | NULL | Maths with an unknown is unknown |
Think of NULL as "maybe true, maybe false". If the answer is the same either way, SQL gives it; otherwise the answer is unknown.
COALESCE: a fallback value
SELECT name, COALESCE(email, 'not given') AS email
FROM students;
COALESCE(a, b, …) returns the first argument that is not NULL. Use it to show something friendly instead of a blank. The fallback must be the same type as the column: text for email, a number for CGPA (COALESCE(cgpa, 0)).
= NULL and <> NULL are never true, not even for NULL rows. Always write IS NULL / IS NOT NULL. And any filter like dept_id <> 3 silently drops NULL rows. If they belong in the answer, add OR dept_id IS NULL, or use dept_id IS DISTINCT FROM 3, which treats NULL as an ordinary value.
MySQL also has IFNULL(a, b), a two-argument COALESCE, and the NULL-safe equals a <=> b. Postgres spells those COALESCE(a, b) and a IS NOT DISTINCT FROM b. COALESCE works in both.
The most asked NULL question in interviews: "Why does my <> or NOT IN filter lose rows?" Answer with three-valued logic: comparisons with NULL are unknown, and WHERE keeps only true. Exams also ask for the AND/OR truth table with unknown: learn the table above.
In one line
NULL means unknown: any comparison with it is unknown, WHERE drops unknown rows, so test with IS NULL and fill gaps with COALESCE.