How Do You Handle NULL Values in SQL?
Learn how to handle NULL in SQL using IS NULL, COALESCE, NULLIF and three-valued logic, with clear examples and common interview mistakes to avoid.
Expected Interview Answer
NULL in SQL means an unknown or missing value, not zero or an empty string, so you handle it with the special IS NULL / IS NOT NULL operators and functions like COALESCE, NULLIF, and IFNULL rather than the normal comparison operators.
Because NULL is unknown, any arithmetic or comparison involving it yields NULL (three-valued logic: TRUE, FALSE, UNKNOWN), which is why WHERE col = NULL never matches rows. Use IS NULL to test for it, COALESCE to substitute a default from the first non-null argument, NULLIF to convert a sentinel value back to NULL, and be aware that aggregates like COUNT(col) and AVG(col) skip NULLs while COUNT(*) does not.
- Prevents silently wrong results from = NULL comparisons
- COALESCE supplies sensible defaults in output
- Keeps aggregates accurate by understanding NULL skipping
- Avoids join and filter rows disappearing unexpectedly
- Makes intent explicit with IS NULL / IS NOT NULL
AI Mentor Explanation
A NULL is like a batter's score shown as a blank on the scorecard because their innings has not been recorded yet — it is not the same as being out for zero. If you ask 'did this player score equal to nothing?' the answer is genuinely unknown, so you must ask 'is this cell still blank?' instead of comparing it to a number.
Step-by-Step Explanation
Step 1
Recognise NULL as unknown
Treat NULL as a missing value, not zero or an empty string — it participates in three-valued logic (TRUE, FALSE, UNKNOWN).
Step 2
Test with IS NULL
Filter missing values using WHERE col IS NULL or col IS NOT NULL, never WHERE col = NULL, which always returns no rows.
Step 3
Substitute with COALESCE
Use COALESCE(col, fallback) to return the first non-null argument, giving output a sensible default value.
Step 4
Neutralise sentinels with NULLIF
Use NULLIF(a, b) to turn a placeholder like an empty string or -1 back into NULL when it means 'unknown'.
Step 5
Mind aggregates and joins
Remember COUNT(col) and AVG skip NULLs, and NULLs never match in equijoins, so outer joins or explicit checks are needed.
What Interviewer Expects
- Clear statement that NULL means unknown, not zero
- Knowledge of IS NULL / IS NOT NULL vs = NULL
- Fluent use of COALESCE, NULLIF and IFNULL
- Awareness of three-valued logic in WHERE clauses
- How aggregates and joins treat NULL differently
Common Mistakes
- Using WHERE col = NULL instead of IS NULL
- Assuming NULL equals zero or an empty string
- Expecting COUNT(col) to include NULL rows
- Forgetting NULLs vanish in inner joins on that column
- Not defaulting NULLs before arithmetic, producing NULL totals
Best Answer (HR Friendly)
“NULL in SQL means the value is simply unknown or missing, which is different from zero. So instead of comparing it like a normal number, you check it with special keywords such as IS NULL, and you can fill in a default using a function called COALESCE.”
Code Example
-- Wrong: this never returns rows
SELECT * FROM employees WHERE manager_id = NULL;
-- Right: use IS NULL
SELECT * FROM employees WHERE manager_id IS NULL;
-- Provide a default in output
SELECT name, COALESCE(commission, 0) AS commission
FROM employees;
-- Turn a sentinel back into NULL, then default it
SELECT name, COALESCE(NULLIF(region, 'UNKNOWN'), 'Unassigned') AS region
FROM employees;Follow-up Questions
- What is three-valued logic in SQL?
- How do COUNT(*), COUNT(col) and COUNT(DISTINCT col) treat NULLs?
- What is the difference between COALESCE and ISNULL/IFNULL?
- Why can NULLs cause rows to disappear in an inner join?
- How does ORDER BY sort NULL values?
MCQ Practice
1. Which predicate correctly finds rows where email has no value?
NULL cannot be compared with =; IS NULL is the only correct test for a missing value.
2. What does COALESCE(NULL, NULL, 5, 8) return?
COALESCE returns the first non-null argument, which is 5.
3. How does COUNT(col) treat NULL values in that column?
COUNT(col) ignores NULLs, whereas COUNT(*) counts every row regardless of NULLs.
Flash Cards
What does NULL represent in SQL? — An unknown or missing value — not zero and not an empty string.
How do you test for NULL? — Use IS NULL or IS NOT NULL; comparison operators like = always yield UNKNOWN with NULL.
What does COALESCE do? — Returns the first non-null value from its argument list, useful for supplying defaults.
What does NULLIF(a, b) return? — NULL when a equals b, otherwise a — handy for converting sentinel values back to NULL.
Do aggregates count NULLs? — COUNT(*) counts all rows; COUNT(col), SUM and AVG ignore NULL values in that column.