Lesson Description
NULL is "unknown" — comparisons need IS NULL.
Lesson 03
Lesson Description
NULL is "unknown" — comparisons need IS NULL.
Easy Project
Mini project: Turn this lesson into a real-world query artifact by writing one clean business report query and validating output quality.
Sooner or later every analyst writes a filter that looks perfectly correct and returns the wrong rows. Nine times out of ten, the cause is NULL.
NULL is SQL's marker for "no value here". It can mean the value is unknown (we never recorded a patient's gender), missing (an office has no state), or not applicable (the CEO has no manager). Crucially, NULL is not zero, not an empty string and not false. It is the absence of a value, and SQL treats it with special rules.
A concrete question from the HR sandbox: "Which office locations have no state or province recorded?" The locations table has 10 offices. Only the four in the United States and Canada have a state; the six in Italy, the United Kingdom, Germany, the Netherlands and Mexico have NULL. Answering that question, and avoiding the traps around it, is what this lesson is about.
WHERE column IS NULL
WHERE column IS NOT NULL
WHERE a IS DISTINCT FROM b -- NULL-safe "not equal"
WHERE a IS NOT DISTINCT FROM b -- NULL-safe "equal"
COALESCE(value1, value2, ...) -- first non-NULL argument
NULLIF(a, b) -- NULL when a = b, otherwise a
SELECT city, country
FROM locations
WHERE state IS NULL;
This returns 6 rows: Roma, London, Oxford, Munich, Utrecht and Mexico City. Flip it to IS NOT NULL and you get the other 4 (Southlake, South San Francisco, Seattle, Toronto). Together they always add up to the full table, which is a useful sanity check.
The same idea works for the employee hierarchy:
SELECT first_name, last_name
FROM employees
WHERE manager_id IS NULL;
Exactly 1 row: Steven King, the president, who reports to nobody. The other 39 employees have a manager.
= NULL returns nothingHere is what most people try first:
SELECT first_name, last_name
FROM employees
WHERE manager_id = NULL;
There is no error. It simply returns 0 rows, even though Steven King's manager_id is NULL. The reason: any comparison with NULL produces unknown, not true. You can see it directly:
SELECT NULL = NULL AS eq,
NULL IS NULL AS is_null,
1 + NULL AS plus,
'abc' || NULL AS concat;
The result is one row where eq is NULL, is_null is true, plus is NULL and concat is NULL. Two unknown values are not known to be equal, so NULL = NULL is itself unknown. And arithmetic or string concatenation with NULL produces NULL: if you do not know one ingredient, you do not know the result.
Since WHERE keeps only rows where the condition is true, rows where it is unknown are dropped. IS NULL is the one test that asks the right question: "is this value missing?"
<>Business question: which offices are outside Texas?
SELECT city, state
FROM locations
WHERE state <> 'TX';
You would expect 9 rows. You get 3: South San Francisco, Seattle and Toronto. The six offices with no state are silently dropped, because NULL <> 'TX' is unknown.
There are two fixes. The explicit one:
SELECT city, state
FROM locations
WHERE state <> 'TX' OR state IS NULL;
Or the NULL-safe comparison operator:
SELECT city, state
FROM locations
WHERE state IS DISTINCT FROM 'TX';
Both return all 9 non-Texas offices. IS DISTINCT FROM treats NULL as an ordinary value for comparison purposes: NULL is distinct from 'TX', and NULL is not distinct from NULL. It never returns unknown.
Reports look better with something more helpful than a blank cell. COALESCE returns its first argument that is not NULL:
SELECT city, COALESCE(state, country) AS state_or_country
FROM locations
ORDER BY city;
All 10 rows come back. Seattle shows WA, while London shows United Kingdom because its state was NULL and COALESCE fell through to the country.
The arguments must have compatible types. manager_id is an integer, so to print a word instead you have to convert it to text first:
SELECT first_name, last_name,
COALESCE(manager_id::text, 'none') AS manager
FROM employees
WHERE department_id = 90;
The Executive department has 3 rows: Steven King shows none, while Neena Kochhar and Lex De Haan show 100.
A related trick: || returns NULL if any piece is NULL, so city || ', ' || state gives NULL for Roma. The function concat_ws(', ', city, state) skips NULL arguments and returns plain Roma instead.
Aggregates are covered properly in the next course, but one detail belongs here. COUNT(*) counts rows; COUNT(column) counts non-NULL values in that column:
SELECT COUNT(*) AS all_rows,
COUNT(state) AS with_state,
COUNT(*) - COUNT(state) AS missing_state
FROM locations;
One row: 10, 4 and 6. This is a quick data-quality check you can run on any column. SUM, AVG, MIN and MAX also skip NULLs, so AVG over a column with gaps averages only the known values.
Every condition in SQL evaluates to one of three values: true, false or unknown. WHERE, JOIN ... ON and HAVING all keep a row only when the result is true. (A CHECK constraint is the odd one out: it rejects a row only when the result is false, so unknown passes.)
| Expression | Result |
|---|---|
NULL = 5, NULL <> 5, NULL > 5 | unknown |
NULL = NULL | unknown |
NULL IS NULL | true |
NULL IS DISTINCT FROM 5 | true |
true OR unknown | true |
false AND unknown | false |
NOT unknown | unknown |
The last row is why NOT cannot rescue you: negating unknown leaves it unknown.
The most famous NULL trap involves NOT IN. Since x NOT IN (a, b) means x <> a AND x <> b, a single NULL in the list poisons the result:
SELECT city, state
FROM locations
WHERE state NOT IN ('TX', NULL);
This returns 0 rows. For Seattle, 'WA' <> 'TX' is true but 'WA' <> NULL is unknown, and true AND unknown is unknown. No row can ever pass. Hard-coding NULL in a list looks silly, but the same thing happens when the list comes from a subquery whose column contains NULLs. You will meet that case with real data in the next lesson on EXISTS.
PostgreSQL sorts NULLs as if they were larger than every other value: they come last in ascending order and first in descending order. ORDER BY state puts CA, ON, TX, WA and then six NULLs. You can override this with ORDER BY state NULLS FIRST or ORDER BY state DESC NULLS LAST.
Mistake 1: = NULL or <> NULL. Both always produce unknown, so the query returns 0 rows with no error. Fix: IS NULL or IS NOT NULL.
Mistake 2: forgetting that <> and NOT skip NULLs. WHERE state <> 'TX' returns 3 rows, not 9. Fix: add OR state IS NULL, or use IS DISTINCT FROM.
Mistake 3: mixing types in COALESCE.
SELECT COALESCE(manager_id, 'none') FROM employees;
PostgreSQL tries to read 'none' as an integer and fails with invalid input syntax for type integer: "none". Fix: cast the column, COALESCE(manager_id::text, 'none').
Mistake 4: dividing by a column that might be zero. 100 / 0 raises division by zero. Wrapping the divisor in NULLIF(divisor, 0) turns a zero into NULL, so the result becomes NULL instead of an error: SELECT 100 / NULLIF(0, 0) returns NULL.
IS NULL, IS NOT NULL, COALESCE and NULLIF are standard and work everywhere.IS DISTINCT FROM is standard SQL, supported by PostgreSQL, SQLite (3.39+) and SQL Server 2022. MySQL uses its own NULL-safe equality operator <=> instead.'' as NULL. PostgreSQL does not: '' is a real, zero-length string.The exercises below this lesson ask you to:
AND from the first lesson.NULL means "no value". It is not 0, not '' and not false.NULL = NULL, produces unknown, and WHERE drops unknown rows. Use IS NULL and IS NOT NULL.<>, NOT and NOT IN silently skip NULL rows; IS DISTINCT FROM is the NULL-safe alternative.COALESCE supplies a fallback value, NULLIF turns a specific value into NULL.COUNT(*) counts rows, COUNT(column) counts non-NULL values.NULL means "unknown", and it changes how every comparison behaves. Missing department ids, empty end dates and optional fields are everywhere in real data, so knowing how NULL flows through filters, arithmetic and aggregates prevents numbers from quietly going wrong.
Finding and replacing missing values
SELECT first_name,
COALESCE(department_id::text, 'Unassigned') AS department
FROM employees
WHERE department_id IS NULL;IS NULL is the only reliable test for missing values. COALESCE returns the first non-NULL argument, which is useful for display labels.
NULLs in aggregates
SELECT COUNT(*) AS all_rows,
COUNT(department_id) AS with_department
FROM employees;COUNT(*) counts rows; COUNT(column) skips NULLs. The difference tells you how many employees lack a department.
COUNT(*) is 5, COUNT(salary) is 3, and AVG divides the sum of the three known salaries by 3, not by 5.
Concept check
3 quick questions. One at a time. Instant score at the end.
Return first_name and last_name of HR employees who have NO manager (manager_id is NULL).
No results yet
Run a query to see results here
From the hospital sandbox, return first_name and last_name of patients whose gender is recorded (not NULL).
No results yet
Run a query to see results here
Return first_name and last_name of employees who have both a manager_id and a department_id (neither is NULL). Order by last_name.
No results yet
Run a query to see results here