Question 7 of 20 Fundamentals Concept
Why does WHERE column = NULL return no rows in SQL?
Short answer
NULL means unknown, so comparing anything to NULL with = gives unknown, not true, and WHERE keeps only rows where the condition is true. Test for missing values with IS NULL, and use IS NOT DISTINCT FROM when two nullable columns should match.
Why
SQL uses three-valued logic: true, false and unknown. The PostgreSQL documentation states that ordinary comparison operators yield null when either input is null, so 7 = NULL is null and so is 7 <> NULL. A WHERE clause discards rows where the condition is false or null.
That is why WHERE result_flag = NULL returns nothing even when half the column is empty, and also why WHERE result_flag <> 'FAIL' silently drops every row whose flag is missing. The correct test is IS NULL or IS NOT NULL. When comparing two nullable columns, for example an old and a new value during a data migration, old_value <> new_value misses rows where one side became null. old_value IS DISTINCT FROM new_value treats null as a comparable value and catches them.
The same logic explains other surprises: NOT IN against a list containing NULL returns no rows, and aggregates such as AVG skip nulls. Some tools offer a setting that makes = NULL behave as IS NULL, and PostgreSQL's documentation strongly advises fixing the application instead, because that behavior is not standard.
A strong answer covers
- NULL means unknown, not empty or zero
- Comparisons with NULL yield unknown
- WHERE keeps only rows where the condition is true
- Use IS NULL and IS DISTINCT FROM
- A not-equal filter also drops NULL rows
Common mistakes
- Writing WHERE status <> 'FAIL' and assuming rows with no status are included
- Treating NULL as equal to an empty string or zero
The interviewer might ask next
How would you write a change-detection query that catches a value changing to or from NULL?