SQL NULL Handling: The Complete Guide to Avoiding Silent Bugs

NULLs are the source of more SQL bugs than almost any other concept.

The worst part: queries with NULL bugs often do not throw an error. They just return wrong results silently — and you might not notice until someone catches a mistake in a report.

This guide explains exactly how NULLs work, the traps they set, and how to handle them correctly every time.

1. What does NULL actually mean?

NULL means unknown or missing. It is not zero, not an empty string, not false. It is the absence of a value.

Examples of what NULL represents in practice:

• A customer has not provided their phone number → phone = NULL
• An order has not shipped yet → shipped_at = NULL
• A product has no category assigned → category_id = NULL

NULL is not a value — it is a state. And that changes how every comparison involving it works.

2. The most important rule: NULL = NULL is not TRUE

This is the rule that catches almost every beginner:

sql
-- You might expect this to work:
SELECT * FROM customers WHERE email = NULL;

-- But it returns ZERO rows. Always.

Why? Because NULL = NULL evaluates to NULL, not TRUE. In SQL, any comparison with NULL using =, <>, >, < returns NULL — not TRUE or FALSE.

The correct way to check for NULL:

sql
SELECT * FROM customers WHERE email IS NULL;

SELECT * FROM customers WHERE email IS NOT NULL;

Always use IS NULL and IS NOT NULL. Never = NULL.

3. NULLs in WHERE clauses — the silent filter

This is where NULL bugs hide most often.

Say you want customers who were NOT referred by user 2:

sql
-- Looks correct but is WRONG:
SELECT * FROM customers WHERE referee_id <> 2;

This query silently drops all customers where referee_id IS NULL — because NULL <> 2 evaluates to NULL, which is not TRUE, so those rows are excluded.

The correct query:

sql
SELECT * FROM customers
WHERE referee_id <> 2
   OR referee_id IS NULL;

Interview note: This is a very common interview trick. If you see a question about filtering with <> on a nullable column, always ask whether NULLs should be included or excluded.

4. NULLs in JOINs

NULL join keys never match.

sql
-- If customer_id is NULL in either table, the JOIN will not match
SELECT c.name, o.order_id
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

A row where customer_id IS NULL in orders will never appear in this result — even in a LEFT JOIN from orders.

This matters for data quality: if you have orphaned rows with NULL foreign keys, they will silently disappear from joined results.

5. NULLs in aggregates

Aggregate functions ignore NULLs — except COUNT(*).

sql
-- Table: scores(student_id, score)
-- Scores: 80, 90, NULL, 70, NULL

SELECT
  COUNT(*) AS total_rows,        -- 5  (counts all rows)
  COUNT(score) AS scored_rows,   -- 3  (ignores NULLs)
  SUM(score) AS total,           -- 240 (ignores NULLs)
  AVG(score) AS average          -- 80  (240 / 3, not 240 / 5!)
FROM scores;

AVG divides by the count of non-NULL values, not total rows. If NULLs mean "zero scored", this gives you a misleading average.

Fix: use COALESCE to convert NULLs to a default before aggregating:

sql
SELECT AVG(COALESCE(score, 0)) AS avg_including_zeros
FROM scores;
-- Result: 48  (240 / 5)

Which approach is correct depends entirely on what NULL means in your context. Always clarify before defaulting.

6. NULLs in ORDER BY

By default:

• In Postgres, NULLs sort last for ASC and first for DESC.
• In MySQL, NULLs sort first for ASC.
• In BigQuery, NULLs sort last for ASC.

This inconsistency across databases has caused many bugs in reporting queries.

Control NULL sort position explicitly:

sql
-- Postgres: put NULLs last in a DESC sort
SELECT name, score
FROM students
ORDER BY score DESC NULLS LAST;

-- Or first
ORDER BY score DESC NULLS FIRST;

7. COALESCE — return the first non-NULL value

COALESCE(val1, val2, val3, ...) returns the first non-NULL argument.

sql
-- Use a default when phone is missing
SELECT
  name,
  COALESCE(phone, 'Not provided') AS phone_display
FROM customers;

sql
-- Use billing address if shipping address is missing
SELECT
  order_id,
  COALESCE(shipping_address, billing_address, 'No address') AS delivery_address
FROM orders;

COALESCE is the standard and most portable way to handle NULL substitution in SQL.

8. NULLIF — return NULL when two values are equal

NULLIF(val1, val2) returns NULL if the two values are equal, otherwise returns val1.

Primary use case: prevent division by zero.

sql
SELECT
  revenue,
  costs,
  revenue / NULLIF(costs, 0) AS ratio
FROM financials;

Without NULLIF, dividing by zero causes an error. With it, the result is NULL when costs = 0 — which you can then handle with COALESCE if needed.

9. IS DISTINCT FROM — NULL-safe comparison

Standard = treats NULL = NULL as NULL (unknown). IS DISTINCT FROM treats NULLs as comparable:

sql
-- Standard comparison: NULL = NULL is NULL (not TRUE)
SELECT NULL = NULL;   -- returns NULL

-- IS DISTINCT FROM: NULL IS DISTINCT FROM NULL is FALSE
SELECT NULL IS NOT DISTINCT FROM NULL;  -- returns TRUE

Use IS NOT DISTINCT FROM when you want to treat NULL as equal to NULL in comparisons — for example, when comparing two nullable foreign keys.

sql
-- Find rows where category changed (including NULL to/from a value)
SELECT *
FROM product_changes
WHERE old_category IS DISTINCT FROM new_category;

10. NULLs in CASE expressions

CASE evaluates conditions in order and returns the first match. If no condition matches and there is no ELSE, the result is NULL.

sql
SELECT
  name,
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    ELSE 'Below C'
  END AS grade
FROM students;

If score IS NULL, none of the conditions match, so the CASE returns 'Below C' here (because of ELSE). Without ELSE, it would return NULL.

To handle NULLs explicitly in a CASE:

sql
CASE
  WHEN score IS NULL THEN 'Not graded'
  WHEN score >= 90 THEN 'A'
  ...
END

11. Quick reference: NULL survival cheat sheet

12. Where to practice NULL handling

The best way to internalize NULL behavior is to encounter it in realistic problems where getting it wrong produces subtly wrong results.

On TheQueryLab, several problems are specifically designed to test NULL awareness — particularly in join and filter patterns that silently drop rows when handled incorrectly.

Start with the SQL Interview Preparation Kit and pay special attention to problems tagged around filtering and joins. When your query is correct, you will know you have truly understood NULLs.