SQL / SELECTING ROWS
Finding missing values with IS NULL
Find rows where a column holds no value using IS NULL and IS NOT NULL, and see why comparing a column to NULL with = never matches.
What you will learn
- Test for missing values with IS NULL and IS NOT NULL, never with = NULL or <> NULL
- Explain why comparing anything to NULL is unknown and why WHERE drops unknown rows
- Tell NULL apart from 0 and from the empty string when writing filters
- Add OR col IS NULL so a <> filter does not silently lose the missing rows
Understanding Finding missing values with IS NULL
NULL is the marker a database stores in a column when no value was recorded there. It is not zero, not an empty string, and not a space; it is the absence of any value. Because NULL stands for something unknown, the database cannot decide whether it equals 10, or '2026-02-01', or even another NULL, so any comparison that touches it produces a third result besides true and false: unknown.
WHERE keeps a row only when its condition comes back true, and unknown is not true. That is why WHERE shipped_on = NULL returns zero rows on a table full of missing dates: every comparison evaluated to unknown, so every row was discarded, and the query looks like proof that nothing is missing. IS NULL is a separate operator that asks whether the marker is there rather than what value sits in the column, so it always answers true or false and never unknown.
The practical consequence is that a condition and its apparent opposite do not always add up to the whole table. WHERE shipped_on <> '2026-02-01' skips the rows where shipped_on is NULL, and so does WHERE shipped_on = '2026-02-01', leaving those rows in the gap between the two queries. Whenever missing data should count as a match, you have to say so explicitly with OR shipped_on IS NULL.
CREATE TABLE orders (
id INTEGER,
customer TEXT,
shipped_on TEXT,
discount INTEGER
);
INSERT INTO orders (id, customer, shipped_on, discount) VALUES
(1, 'Ada', '2026-02-01', 10),
(2, 'Bello', NULL, 0),
(3, 'Chen', '2026-02-03', NULL),
(4, 'Dara', NULL, NULL);
SELECT id, customer
FROM orders
WHERE shipped_on IS NULL;NULL means no value was recorded, so it can only be detected with IS NULL, never matched with a comparison operator.
Worked examples
IS NOT NULL keeps a stored zero
Shows that a discount of 0 counts as a recorded value while a missing discount does not.
SELECT id, customer, discount
FROM orders
WHERE shipped_on IS NULL
AND discount IS NOT NULL;Example explained
Line 1shipped_on IS NULL is true for orders 2 and 4, the two with no shipping date.
Line 2discount IS NOT NULL is true for order 2 because 0 is a value that was actually stored.
Line 3For order 4 the discount is missing, so IS NOT NULL is false and the AND fails.
Line 4The result proves the point visually: the surviving row shows 0, not an empty cell.
How <> loses the missing rows
Demonstrates that a not-equal filter silently discards rows where the column is NULL.
SELECT id, customer, shipped_on
FROM orders
WHERE shipped_on <> '2026-02-01';Example explained
Line 1Order 1 is rejected correctly: its date equals the literal, so the test is false.
Line 2Orders 2 and 4 compare NULL with a date, which evaluates to unknown, so WHERE drops them.
Line 3Only order 3 evaluates to true, so reading this query as everything except 1 February is wrong.
Line 4Three of the four orders were not shipped on 1 February, yet the result shows one.
Putting the unknown rows back
Repairs the previous filter by naming the missing values with an explicit IS NULL branch.
SELECT id, customer
FROM orders
WHERE shipped_on IS NULL
OR shipped_on <> '2026-02-01';Example explained
Line 1shipped_on IS NULL recovers orders 2 and 4, which the comparison alone could never keep.
Line 2OR needs only one side to be true, so order 3 still qualifies through the comparison.
Line 3Order 1 fails both branches: its date is present and it equals the excluded value.
Line 4The two branches never overlap, because a column cannot be both NULL and comparable.
Important notes
Most clients print NULL as an empty cell, exactly like an empty string, so you cannot tell the two apart by looking; psql \pset null and the sqlite3 .nullvalue setting make NULL visible.
IS NULL judges one column of one row at a time. A row is never NULL as a whole, and a NULL in one column only affects conditions that mention that column.
Common mistakes
Writing WHERE shipped_on = NULL or WHERE shipped_on <> NULL: both are valid SQL and run without error, but every comparison is unknown so you get zero rows and wrongly conclude no data is missing.
Treating NULL as 0 or as '': WHERE discount = 0 returns only order 2 and misses the orders where no discount was recorded, while WHERE discount IS NULL returns those and misses order 2. They are different sets of rows, never interchangeable.
Assuming a filter and its negation cover the table, then reporting totals from WHERE shipped_on = '2026-02-01' and WHERE shipped_on <> '2026-02-01' that add up to 2 out of 4 rows because the NULL rows fell into neither.
Try it yourself
Change, predict, then run
Using the orders table above, write one query that lists id and customer for the orders with no shipping date and no discount recorded, then change it so it returns the orders that are missing exactly one of the two.
Open the SQL workspaceCheck your understanding
The orders table has 4 rows. WHERE discount = 0 returns 1 row and WHERE discount <> 0 returns 1 row. Why do the two results not account for all 4 rows?
- The two rows whose discount is NULL evaluate to unknown in both comparisons, and WHERE keeps only true
- The <> operator excludes the value 0 on both sides, so one row is counted in neither query
- Rows with a NULL discount are returned by discount <> 0 only once you wrap the condition in parentheses
- A WHERE clause always skips rows that contain a NULL in any of their columns
Show answer
Comparing NULL with 0 yields unknown rather than true or false, so those two rows are discarded by both filters and only IS NULL can find them. The last option is tempting because NULLs do seem to disappear, but a NULL only affects the condition that mentions its column: order 3 has a NULL discount and is still returned by a filter on shipped_on.