SQL NULL Handling Explained — IS NULL, COALESCE and Common Traps

Every real-world database has missing data. A customer who didn't provide a phone number. An order with no shipping date yet because it hasn't shipped. An employee whose bonus hasn't been decided. These gaps are everywhere, and SQL has a special way of representing them — NULL. If you don't understand NULL, your queries will silently return wrong answers without throwing a single error, which is one of the most dangerous bugs a database can have.

The tricky part is that NULL breaks the rules you're used to. In normal life, if you ask 'is blank equal to blank?' you'd say yes. SQL says 'I don't know' — and that 'I don't know' answer is what causes queries to behave unexpectedly. Filters exclude rows you expected to include, calculations return NULL when you expected a number, and JOINs quietly drop data. SQL gives you specific tools — IS NULL, IS NOT NULL, COALESCE, and NULLIF — to handle these gaps safely and deliberately.

By the end of this article you'll understand what NULL actually means (and what it doesn't), why you can't use = NULL in a WHERE clause, how to filter and replace NULLs with confidence, and how to avoid the silent bugs that trip up even experienced developers. You'll be writing NULL-safe SQL from scratch.

What NULL Actually Is — and Why = NULL Never Works

NULL isn't a value — it's the absence of a value. Think of it like a sealed envelope. You don't know what's inside. If someone hands you two sealed envelopes and asks 'are these the same?', the honest answer is 'I have no idea' — not yes, not no. SQL agrees. Comparing anything to NULL using = produces a third logical state called UNKNOWN, which is neither TRUE nor FALSE.

This is why WHERE salary = NULL will never return any rows, even if the salary column genuinely contains NULLs. SQL evaluates the condition, gets UNKNOWN for every row, and excludes them all. WHERE only keeps rows where the condition is TRUE.

SQL gives you a dedicated operator for NULL checks: IS NULL and IS NOT NULL. These are the only correct ways to test whether a value is absent. They return TRUE or FALSE — no UNKNOWN — which is exactly what WHERE needs to filter rows correctly.

This also means NULL = NULL is NOT true in SQL. Two unknown things are not automatically equal. This catches beginners off guard constantly, especially when writing JOIN conditions.

-- Create a simple employees table to demonstrate NULL behavior
CREATE TABLE employees (
    employee_id   INT PRIMARY KEY,
    full_name     VARCHAR(100) NOT NULL,
    department    VARCHAR(50),
    phone_number  VARCHAR(20),   -- nullable: not every employee has provided one
    annual_bonus  DECIMAL(10,2)  -- nullable: bonus not yet decided for some staff
);

-- Insert sample rows — some have NULLs, some don't
INSERT INTO employees VALUES (1, 'Alice Mercer',   'Engineering', '555-0101', 4500.00);
INSERT INTO employees VALUES (2, 'Ben Okafor',     'Marketing',   NULL,       3200.00);
INSERT INTO employees VALUES (3, 'Clara Huang',    'Engineering', '555-0303', NULL);
INSERT INTO employees VALUES (4, 'David Nkosi',    'HR',          NULL,       NULL);
INSERT INTO employees VALUES (5, 'Eva Johansson',  'Marketing',   '555-0505', 2800.00);

-- ❌ WRONG: This returns 0 rows even though NULLs exist in phone_number
-- WHERE evaluates phone_number = NULL as UNKNOWN for every row
SELECT full_name
FROM   employees
WHERE  phone_number = NULL;
-- Output: (0 rows) — silent bug!

-- ✅ CORRECT: Use IS NULL to find employees without a phone number
SELECT full_name
FROM   employees
WHERE  phone_number IS NULL;
-- Output: Ben Okafor, David Nkosi

-- ✅ CORRECT: Use IS NOT NULL to find employees who DO have a phone number
SELECT full_name, phone_number
FROM   employees
WHERE  phone_number IS NOT NULL;
-- Output: Alice Mercer | 555-0101
--         Clara Huang  | 555-0303
--         Eva Johansson| 555-0505

COALESCE — Replacing NULL With a Sensible Default

Now that you can detect NULLs, the next question is: what do you do with them? Often you want to replace a NULL with a default value so your reports and application logic get something useful instead of a blank.

That's exactly what COALESCE does. You give it a list of values and it hands back the first one that isn't NULL. Think of it like a backup plan — 'give me the first option, but if that's blank, try the second, and if that's blank too, try the third.'

Coalesce is standard SQL and works in MySQL, PostgreSQL, SQL Server, SQLite, and Oracle. It's one of the most-used NULL-handling functions in production databases.

A key use case is calculated columns. If annual_bonus is NULL and you add it to a base salary, the entire result becomes NULL — SQL says 'I can't add a known number to an unknown number and give you a real answer.' COALESCE lets you substitute zero (or any default) so your arithmetic stays intact.

COALESCE can take more than two arguments. COALESCE(a, b, c, d) returns the first non-NULL across all four — really useful when you have primary, secondary, and fallback data sources.

-- COALESCE(value_to_check, fallback_if_null)
-- Returns the first argument that is NOT NULL

-- Use case 1: Display a friendly label instead of NULL in a report
SELECT
    full_name,
    COALESCE(phone_number, 'No phone on file') AS contact_number
FROM employees;

-- Use case 2: Safe arithmetic — treat a missing bonus as 0
-- Without COALESCE, NULL bonus would make the entire total NULL
SELECT
    full_name,
    80000                             AS base_salary,
    COALESCE(annual_bonus, 0)         AS bonus,           -- swap NULL for 0
    80000 + COALESCE(annual_bonus, 0) AS total_compensation
FROM employees;

-- Use case 3: Multiple fallbacks — try preferred_email, then work_email, then a default
-- (Imaginary columns to show the pattern)
-- COALESCE(preferred_email, work_email, 'no-email@company.com')

-- Use case 4: COALESCE in a WHERE clause to filter on computed values
SELECT full_name
FROM   employees
WHERE  COALESCE(annual_bonus, 0) < 3000;  -- treats NULL bonus as 0 for comparison

NULLIF and Aggregate Functions — NULLs You Create on Purpose

COALESCE turns NULLs into real values. NULLIF does the opposite — it turns a specific real value into NULL. Why on earth would you want that?

The classic example is preventing division by zero. If you divide by zero in SQL, you get an error. But if you use NULLIF(denominator, 0), SQL turns any zero denominator into NULL — and dividing by NULL returns NULL instead of crashing. You can then wrap that in COALESCE to display something readable.

Aggregate functions like COUNT, SUM, AVG, MIN, and MAX have a specific and important relationship with NULL: they all silently skip NULL values. This is usually what you want — averaging a salary column where some salaries aren't set yet should average the known salaries, not treat unknowns as zero. But it means COUNT(*) and COUNT(column_name) give different answers, which surprises many beginners.

COUNT(*) counts every row. COUNT(phone_number) counts only rows where phone_number is not NULL. This distinction matters enormously in reports.

-- NULLIF(expression, unwanted_value)
-- Returns NULL if expression equals unwanted_value, otherwise returns expression

-- Use case: safe division — avoid divide-by-zero error
-- Imagine a sales table with targets and actual sales
CREATE TABLE sales_performance (
    rep_name    VARCHAR(100),
    target      INT,
    actual_sold INT
);

INSERT INTO sales_performance VALUES ('Nia Patel',    100, 85);
INSERT INTO sales_performance VALUES ('Omar Silva',   0,   0);  -- new rep, no target set
INSERT INTO sales_performance VALUES ('Priya Chen',   200, 210);

-- ❌ WRONG: This crashes when target = 0
-- SELECT actual_sold / target FROM sales_performance;

-- ✅ CORRECT: NULLIF converts 0 target to NULL, avoiding the crash
-- COALESCE then turns the NULL result into a readable message
SELECT
    rep_name,
    target,
    actual_sold,
    COALESCE(
        CAST(actual_sold AS DECIMAL) / NULLIF(target, 0) * 100,
        0
    ) AS achievement_pct   -- 0 when target was NULL/zero
FROM sales_performance;

-- ─────────────────────────────────────────
-- Aggregate functions and NULL — key demo
-- ─────────────────────────────────────────

-- COUNT(*) counts ALL rows including those with NULLs
SELECT COUNT(*) AS total_employees
FROM   employees;
-- Returns: 5

-- COUNT(column) counts only NON-NULL values in that column
SELECT COUNT(annual_bonus) AS employees_with_bonus
FROM   employees;
-- Returns: 3  (Clara and David have NULL bonus — they are skipped)

-- AVG also skips NULLs — it averages only the 3 known bonuses
SELECT AVG(annual_bonus) AS average_known_bonus
FROM   employees;
-- Returns: 3500.00  (4500 + 3200 + 2800) / 3

-- If you want NULL bonuses treated as 0 in the average:
SELECT AVG(COALESCE(annual_bonus, 0)) AS average_bonus_including_none
FROM   employees;
-- Returns: 2100.00  (4500 + 3200 + 0 + 0 + 2800) / 5

Frequently Asked Questions