SQL · Queries that survive scale
How does SQL handle NULL values?
Short answer
NULL means unknown or absent, so comparisons produce unknown and you must use IS NULL or IS NOT NULL. Aggregates ignore NULLs, and NOT IN with NULL can produce surprising results.
SQL uses three-valued logic: true, false, and unknown. A WHERE clause keeps only rows where the predicate is true, so a comparison that evaluates to unknown is discarded. NULL is not equal to anything, including another NULL.
SELECT
id,
COALESCE(nickname, first_name, 'anonymous') AS display_name,
NULLIF(score, 0) AS safe_divisor
FROM profiles
WHERE deleted_at IS NULL
AND last_seen_at IS NOT NULL;Rules worth remembering
- Use IS NULL and IS NOT NULL, not = NULL
- NULL = NULL evaluates to unknown, not true
- COALESCE returns the first non-NULL argument
- NULLIF returns NULL when the two arguments are equal
- COUNT(column), SUM, AVG, MIN, and MAX ignore NULL inputs
- UNIQUE constraints often allow multiple NULLs because NULLs are not equal
Decide whether NULL is a valid business state or a data quality defect. If a column must always have a value, declare NOT NULL and enforce it at write time instead of compensating for NULLs in every query.
Follow-ups you should expect
Interviewers use these to check whether the first answer was understanding or memorisation.
- 1Why does = NULL not work?
- 2How does COALESCE differ from NULLIF?
- 3Do aggregate functions treat NULL as zero?
- 4How many NULLs can a UNIQUE constraint allow?
Say it in the interview
NULL represents an unknown or missing value, and SQL uses three-valued logic, so comparisons with NULL return unknown rather than true. I check for it with IS NULL and replace it with COALESCE when a default makes sense. Aggregates ignore NULLs, which changes denominators. I also avoid NOT IN with nullable subqueries and prefer NOT EXISTS.
Use this as a model, not a script. Saying it in your own words is what makes it survive follow-up pressure.
Related questions
Reading is step one
Now say it out loud.
Understanding an answer and delivering it under pressure are different skills. Rehearse this topic in a live SQL mock interview and get scored feedback on depth, clarity and confidence.