Thinking habit: Know what you don't know

SQL != and NULL: why some rows go missing

The question: List every customer who is not from Mumbai, including customers whose city we don't know.

customers

This query looks right. Run it, decide if it is, and fix it.

Show the answer and why the first query is wrong

Why the usual answer is wrong

SELECT name
FROM customers
WHERE city <> 'Mumbai';

For Neha and Meera the city is NULL. NULL <> 'Mumbai' isn't true, it's unknown, and WHERE only keeps rows where the condition is true.

SELECT name
FROM customers
WHERE city <> 'Mumbai' OR city IS NULL;

Any comparison with NULL (=, <>, <, >) gives unknown, so those rows are dropped. Ask for them by name with IS NULL.

The same trap hides in NOT IN and in joins on columns that can be NULL. Whenever a column can be empty, ask what should happen to those rows.

SELECT name
FROM customers
WHERE COALESCE(city, '') <> 'Mumbai';

Practise more like this, on real business data

324 interview-style questions on 35 tables with the duplicates, NULLs and missing weeks interviewers test. Free.