SQLBeginner
What does WHERE x <> 'a' return for rows where x is NULL?
“Explain NULL in SQL and give an example where it silently produces the wrong result.”
What this tests
- Three-valued logic
- Awareness of the NOT IN trap
- Aggregate behaviour with NULL
Answers by level
Read the beginner answer first and notice what is missing.
NULL means "unknown", so any comparison with it is NULL, not true or false. x <> 'a' is NULL when x is NULL, and WHERE keeps only rows where the predicate is true — so rows with NULL are silently dropped.
Use IS NULL / IS NOT NULL to test for it. count(col) skips NULLs while count(*) does not, and sum of no rows is NULL, so wrap it in coalesce(sum(x), 0).
Green flags · Red flags
Strong green flag · Volunteers the NOT IN / nullable-subquery bug unprompted.
Green flags
- Says "unknown", not "empty"
- Knows the NOT IN trap and reaches for NOT EXISTS
- Mentions coalesce for sum
Red flags
- Thinks NULL = NULL is true
- Uses
= NULL - Unaware WHERE drops NULL predicates
Follow-up questions
F1
How would you find users with no orders?
F2
What does count(email) return vs count(*)?
Scenario
A report of "customers who never logged in" using
WHERE last_login NOT IN (SELECT last_login FROM sessions) returns zero rows. Why, and what is the fix?