What they're testing
Whether you know three-valued logic. It's the root of an entire family of silent bugs: NOT IN, <>, and UNIQUE allowing multiple NULLs.
The short answer~30 seconds
It returns NULL — UNKNOWN — not TRUE. NULL means "value unknown", and two unknowns can't be concluded equal. If you want equality that treats two NULLs as equal, use IS NOT DISTINCT FROM in Postgres or the <=> operator in MySQL.
The long answer
The most immediate consequence is that WHERE keeps a row only when the predicate is TRUE. UNKNOWN is discarded exactly like FALSE, so WHERE deleted_at <> '2020-01-01' silently skips every row where deleted_at IS NULL — which is almost certainly not what the author meant.
The expensive trap is NOT IN (subquery). If the subquery yields even one NULL, the whole expression can never be TRUE again and the query returns nothing — no error, no warning. NOT EXISTS doesn't have this problem because it works on row existence rather than value comparison, which is why many teams standardise on it.
The same logic explains why a UNIQUE constraint permits multiple NULL rows in most engines: two NULLs aren't considered duplicates. Postgres 15 added UNIQUE NULLS NOT DISTINCT to invert that when you need it.
SELECT NULL = NULL; -- NULL (không phải true)
SELECT NULL <> 1; -- NULL
SELECT NULL IS NULL; -- true
SELECT 1 IS NOT DISTINCT FROM NULL; -- false, và không bao giờ NULL
-- Trả về 0 hàng nếu blocked_ids chứa dù chỉ một NULL:
SELECT * FROM users WHERE id NOT IN (SELECT blocked_id FROM blocks);
-- An toàn với NULL:
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM blocks b WHERE b.blocked_id = u.id);What they'll ask next
?How do count(*) and count(col) differ?
count(*) counts rows; count(col) counts rows where col is not NULL. The gap between the two is exactly the NULL count — a quick data-quality probe.
?Do NULLs affect indexes?
Yes. Postgres stores NULLs in B-trees, so IS NULL can still use an index; Oracle doesn't store NULLs in a plain B-tree index, so an IS NULL query has to scan. It's an engine difference worth naming rather than generalising.
These lose points
- Answering "TRUE, they're the same". This is a screening question — getting it wrong costs credibility for the rest of the SQL round.
- Treating NULL as 0 or as an empty string. They're three different things, and conflating them is a real source of migration bugs.