Comparisons with null generally produce unknown rather than true or false. WHERE retains only true predicates.
Before you start
You should understand tables, keys, joins and SELECT queries. Write down a tiny dataset before reasoning about a query. SQL examples use PostgreSQL-style syntax where relevant; planner behavior, locking and available features must be checked for the database and version used in an application.
The practical goal is to reason through this situation: Use IS NULL to find missing values rather than equals null. Read the walkthrough first, then try the interview exercise before opening its answer. The important part is explaining the decision and its consequences, rather than remembering a definition alone.
Step-by-step walkthrough
Step 1: Identify nullable inputs
A comparison involving null can evaluate to unknown.
Step 2: Use explicit null predicates
IS NULL tests missingness; equals null does not.
Step 3: Test anti-membership carefully
NOT EXISTS often expresses the intended relationship without NOT IN’s null surprise.
Worked scenario
Use IS NULL to find missing values rather than equals null.
SELECT c.id FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);A null customer_id in orders does not become a matching customer through equality. By contrast, NOT IN over a set containing null can make otherwise absent values evaluate to unknown and disappear from WHERE results.
Common mistake
NOT IN can behave unexpectedly when its set contains null.
Verify the behavior
Include null in the comparison set and compare anti-membership results against expected IDs.
Interview exercise
Find rows without a matching record.
Answer and reasoning
Consider NOT EXISTS with an explicit correlated condition and test null-containing data.
Continue learning
Compare the scenario with the SQL and PostgreSQL interview questions and test your understanding with the SQL and PostgreSQL MCQs. For terminology and implementation details, consult the reference material.