NULL Handling in SQL: COALESCE, NULLIF, IS NULL

Why NULL Is Different From Every Other Value

NULL represents the absence of a value — not zero, not an empty string, not "unknown" in the way a placeholder text would be. It's a genuinely different concept, and SQL's three-valued logic (TRUE, FALSE, and UNKNOWN) around NULL trips up even experienced engineers. Almost every subtle, hard-to-debug SQL bug traces back to a NULL comparison behaving differently than expected.

The Core Rule: NULL Is Never Equal to Anything, Including Itself

SELECT NULL = NULL;      -- returns NULL (unknown), not TRUE
SELECT NULL != NULL;     -- also returns NULL, not TRUE
SELECT 5 = NULL;         -- returns NULL
SELECT NULL IS NULL;     -- returns TRUE — this is the correct way to test for NULL

This is the single most important fact about NULL: you can never use = NULL to test for it. Every comparison involving NULL — equality, inequality, greater than, less than — evaluates to UNKNOWN, not TRUE or FALSE, and a WHERE clause only keeps rows where the condition evaluates to TRUE.

-- Wrong: matches nothing, ever, regardless of the data
SELECT * FROM employees WHERE manager_id = NULL;

-- Correct
SELECT * FROM employees WHERE manager_id IS NULL;
SELECT * FROM employees WHERE manager_id IS NOT NULL;

COALESCE: Substituting a Default Value

COALESCE() returns the first non-NULL value from a list of arguments — the standard way to supply a fallback/default when a value might be missing.

SELECT name, COALESCE(phone, email, 'No contact info') AS best_contact
FROM customers;
-- Uses phone if present; falls back to email if phone is NULL; falls back to the literal string if both are NULL

-- Common pattern: treat NULL as zero for arithmetic
SELECT product_id, COALESCE(SUM(quantity), 0) AS total_sold
FROM order_items
GROUP BY product_id;

COALESCE is standard ANSI SQL and works identically across PostgreSQL, MySQL, and SQL Server. It accepts any number of arguments and short-circuits at the first non-NULL one, evaluating later arguments only if needed.

NULLIF: Turning a Specific Value Into NULL

NULLIF(a, b) returns NULL if a equals b, otherwise returns a. It's essentially the reverse of COALESCE — instead of replacing NULL with a value, it replaces a specific value with NULL.

-- Prevent a divide-by-zero error by turning a zero denominator into NULL
SELECT
  revenue,
  cost,
  revenue / NULLIF(cost, 0) AS revenue_to_cost_ratio
FROM products;
-- If cost is 0, NULLIF returns NULL, and revenue / NULL = NULL (no error), instead of a division-by-zero error

This exact pattern — NULLIF(denominator, 0) — is the standard, idiomatic way to safely guard against division errors in SQL, and shows up constantly in percentage/ratio calculations (see the retention and funnel examples in our product analytics guide).

NULLs in Aggregate Functions

Most aggregate functions silently ignore NULLs — which usually matters more than people expect.

SELECT AVG(salary) FROM employees;
-- Averages only the NON-NULL salary values — NULLs are excluded from both the sum and the count

SELECT COUNT(*) FROM employees;          -- counts ALL rows, including NULLs
SELECT COUNT(salary) FROM employees;     -- counts only rows where salary IS NOT NULL

This distinction between COUNT(*) and COUNT(column) is a very common interview question in its own right — COUNT(*) counts rows; COUNT(column) counts non-NULL values in that specific column, which can produce a meaningfully different number.

NULLs in JOINs

Since NULL = NULL is never TRUE, rows with a NULL in the join column never match anything in a standard JOIN — including another row that also has a NULL in that column.

-- Employees with no manager (manager_id IS NULL) will NEVER match any row
-- in a standard join, even another employee row where manager_id is also NULL
SELECT e.name, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;   -- silently excludes anyone with a NULL manager_id

This is exactly why LEFT JOIN, not INNER JOIN, is the standard choice for self joins on optional relationships — see our self joins guide for the full pattern.

NULLs in Sorting

-- PostgreSQL: explicit control over NULL placement
SELECT * FROM employees ORDER BY commission DESC NULLS LAST;

-- MySQL: no NULLS LAST/FIRST syntax — NULLs sort first in ASC order by default;
-- a common workaround wraps the column: ORDER BY commission IS NULL, commission DESC
SELECT * FROM employees ORDER BY commission IS NULL, commission DESC;

-- SQL Server: also no NULLS LAST — NULLs sort first in ASC by default, similar workaround applies

Default NULL sort order varies by database — PostgreSQL sorts NULLs last in ascending order by default (opposite of most other databases), which is a common surprise for anyone switching between engines. Never assume; always test or specify explicitly.

The NOT IN + NULL Trap

This deserves special attention because it's one of the most common, hardest-to-spot NULL-related bugs in production SQL:

-- If ANY row in archived_departments has a NULL department value,
-- this query silently returns ZERO rows — for every single row in employees
SELECT name FROM employees
WHERE department NOT IN (SELECT department FROM archived_departments);

Why: NOT IN (a, b, NULL) is logically equivalent to != a AND != b AND != NULL, and != NULL always evaluates to UNKNOWN — which, combined with AND, makes the entire condition UNKNOWN for every row, regardless of the other values. Always use NOT EXISTS instead when the subquery's column could contain NULLs — it's immune to this trap entirely.

-- Safe alternative, unaffected by NULLs in the subquery
SELECT e.name FROM employees e
WHERE NOT EXISTS (
  SELECT 1 FROM archived_departments a WHERE a.department = e.department
);

Common Mistakes

• Using = NULL or != NULL instead of IS NULL / IS NOT NULL — the single most common NULL mistake, and one that fails silently (returns no rows) rather than erroring.
• Using NOT IN against a column that might contain NULL — the trap explained above; use NOT EXISTS instead.
• Forgetting that aggregates ignore NULLs — assuming AVG() includes every row when it actually excludes NULLs from both the sum and the count.
• Assuming NULL sort order is consistent across databases — always verify or specify explicitly (NULLS LAST/FIRST where supported).

Common Interview Questions

1. Why does WHERE column = NULL never return any rows? Because NULL is never equal to anything, including itself — every comparison involving NULL evaluates to UNKNOWN, not TRUE.
2. What's the difference between COUNT(*) and COUNT(column)? COUNT(*) counts all rows; COUNT(column) counts only rows where that column is non-NULL.
3. How would you safely calculate a ratio that might divide by zero? Wrap the denominator in NULLIF(denominator, 0), as shown above.
4. Why is NOT IN dangerous with a subquery that might return NULL? Walk through the logical equivalence to a chain of AND conditions, and explain why NOT EXISTS is the safer default.

Frequently Asked Questions

Is NULL the same as an empty string or zero?

No — NULL means "no value is recorded," while an empty string ('') and zero (0) are actual, specific values. Treating them as interchangeable is a common source of both bugs and confusing query results.

Do all databases handle NULL comparisons the same way?

The core three-valued logic (TRUE/FALSE/UNKNOWN) is standard ANSI SQL and consistent across PostgreSQL, MySQL, and SQL Server. Where they differ is in convenience features around NULL — default sort order, and syntax like NULLS LAST — so always verify database-specific behavior for those.

Practice NULL Handling

NULL-related bugs are some of the most common in real production SQL, and interviewers know it — expect at least one question that tests whether you handle NULLs correctly, even if it's not the headline topic. Try it hands-on with our SQL practice questions, or work through messy real-world data in our case studies. For the related CASE WHEN patterns often used alongside NULL handling, see our CASE WHEN guide.