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 NULLThis 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 errorThis 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 NULLThis 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_idThis 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 appliesDefault 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
- 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.
- What's the difference between COUNT(*) and COUNT(column)? COUNT(*) counts all rows; COUNT(column) counts only rows where that column is non-NULL.
- How would you safely calculate a ratio that might divide by zero? Wrap the denominator in NULLIF(denominator, 0), as shown above.
- 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.