Why SQL WHERE column = NULL Returns No Rows: Use IS NULL

Two tasks clearly have no completion time, yet WHERE completed_at = NULL finds no rows. The data has not disappeared. NULL marks a missing or unknown value and is not compared with = like an ordinary value.

How is NULL different from zero?

Tasks 1 and 3 in this small test table have no completion time. This is sample data, not live service data:

CREATE TABLE tasks (
  id INTEGER PRIMARY KEY,
  completed_at TEXT
);

INSERT INTO tasks (id, completed_at) VALUES
  (1, NULL),
  (2, '2024-11-18'),
  (3, NULL),
  (4, '2024-11-20');

NULL is neither the number zero nor a date string. Here it means that no completion time was recorded. It does not universally mean “incomplete”; the schema's designer must define that meaning.

Why does completed_at = NULL find no rows?

This query is a tempting first attempt:

SELECT id
FROM tasks
WHERE completed_at = NULL
ORDER BY id;

It returns zero rows. In standard SQL null semantics, an ordinary comparison with NULL is UNKNOWN, not TRUE or FALSE. WHERE retains only rows for which the condition is true, so tasks 1 and 3 are excluded too. NOT (completed_at = NULL) does not turn UNKNOWN into true.

Which rows does IS NULL return?

Use IS NULL to ask whether a value is missing:

SELECT id
FROM tasks
WHERE completed_at IS NULL
ORDER BY id;

id
1
3

The same four rows remain in the table. Only the condition changed. The diagram contrasts the empty result from = NULL with tasks 1 and 3 returned by IS NULL:

= NULL is never true for these rows. IS NULL checks for the absence of a value and returns the two expected IDs.

Use IS NOT NULL for rows with a value

completed_at <> NULL does not find the dated rows either: its comparison also evaluates to UNKNOWN. Use the opposite predicate instead:

SELECT id
FROM tasks
WHERE completed_at IS NOT NULL
ORDER BY id;

id
2
4

Here IS NULL returns 1 and 3, while IS NOT NULL returns 2 and 4. Both = NULL and <> NULL return zero rows. Check the actual IDs as well as the count to catch conditions that select the wrong rows.

What should you check before UPDATE?

A bad read query merely shows an empty result. The same bad predicate in an UPDATE can silently modify zero rows without a syntax error. First select the intended rows, then confirm the affected count after the change:

SELECT id
FROM tasks
WHERE id = 3 AND completed_at IS NULL;

UPDATE tasks
SET completed_at = '2024-11-21'
WHERE id = 3 AND completed_at IS NULL;

The first statement should find just task 3, and the update should change one row. Including id = 3 prevents changing every task without a completion time. For real data, decide how to verify scope and affected row count before executing a change.

Key takeaways

Use IS NULL and IS NOT NULL, not = or <>, to test for null values. If WHERE column = NULL returns zero rows, do not assume the data is gone. Confirm both the count and identities of target rows before relying on a read or write query.