NULL Handling (COALESCE, IS NULL)

Reviewed & published by Brayan K

NULL is SQL's way of saying "we don't know" — a missing phone number, an unset date, an unanswered field. It follows surprising rules that quietly break naive queries, so handling it correctly is one of the most valuable SQL skills you can build.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

You'll learn why = NULL never works, how to test for missing values with IS NULL, supply defaults with COALESCE, dodge divide-by-zero with NULLIF, and how aggregates quietly skip NULLs.

What You'll Learn

• Why NULL means 'unknown', not zero or empty
• Test for missing values with IS NULL
• Supply defaults with COALESCE
• Avoid divide-by-zero with NULLIF
• Why NULL is contagious in arithmetic
• How aggregates treat (and skip) NULLs

Our Sample Table: contacts

Note the empty cells — those are NULLs, the "we don't have this" markers we'll learn to handle.

(NULL) marks a missing value — there's nothing actually stored, not an empty string.

1. NULL Is Not Zero, Empty, or Equal to Anything

NULL represents the absence of a value. Because it means "unknown", any comparison with it returns UNKNOWN — not true, not false. That's why phone = NULL matches nothing. You must use the dedicated IS NULL / IS NOT NULL operators.

NULL is a sealed envelope with no address written on it. You can't ask "is this address equal to that one?" — there's nothing to compare. You can only ask "is the address missing?" — that's IS NULL.

-- The three tables this lesson uses. The NULLs are deliberate — they are
-- what the lesson is about.
CREATE TABLE contacts (
    id     INTEGER PRIMARY KEY,
    name   TEXT,
    phone  TEXT,
    mobile TEXT,
    email  TEXT
);
INSERT INTO contacts VALUES
    (1, 'Maya',  '555-0101', NULL,       'maya@example.com'),
    (2, 'Leo',   NULL,       '555-0199', 'leo@example.com'),
    (3, 'Priya', NULL,       NULL,       NULL);

CREATE TABLE staff (
    id       INTEGER PRIMARY KEY,
    name     TEXT,
    base_pay INTEGER,
    bonus    INTEGER
);
INSERT INTO staff VALUES
    (1, 'Maya', 5000, 500),
    (2, 'Leo',  4800, NULL),
    (3, 'Sam',  5200, 250);

CREATE TABLE products (
    id      INTEGER PRIMARY KEY,
    name    TEXT,
    revenue INTEGER,
    cost    INTEGER
);
INSERT INTO products VALUES
    (1, 'Wireless Mouse', 2400, 800),
    (2, 'Desk Lamp',      1500,   0),
    (3, 'Notebook',        900, 300);

-- You CANNOT use = with NULL. Use IS NULL / IS NOT NULL.
SELECT name, phone
FROM contacts
WHERE phone IS NULL;          -- contacts with no phone on file

SELECT name, phone
FROM contacts
WHERE phone IS NOT NULL;      -- contacts who DO have a phone

-- ❌ WHERE phone = NULL  returns ZERO rows, always — it's a trap.

-- ✅ Expected result:
-- name | phone
-- Maya | 555-0101

2. COALESCE — Supply a Default

COALESCE takes any number of arguments and returns the first one that isn't NULL. It's the go-to tool for "use this, or fall back to that, or finally show a placeholder" — turning ragged, gap-filled data into clean output.

-- Uses the contacts, staff and products tables from the first example.
-- COALESCE returns the FIRST non-NULL argument
SELECT
    name,
    COALESCE(phone, mobile, 'No contact') AS best_number
FROM contacts;

-- ✅ Expected result:
-- name | best_number
-- Maya | 555-0101
-- Leo | 555-0199
-- Priya | No contact

Your Turn: find the gaps

Add the operator that finds rows where a column has no value.

-- Uses the contacts, staff and products tables from the first example.
-- 🎯 YOUR TURN — fill in the operator, then "Try it Yourself"
-- Goal: list every contact who is MISSING an email address.

SELECT name, email
FROM contacts
WHERE email ___;   -- 👉 the operator that tests for a missing value

-- ✅ Expected: only contacts whose email is NULL.

3. NULL in Arithmetic & NULLIF

NULL is contagious: any arithmetic involving NULL produces NULL, so base_pay + bonus becomes NULL the moment a bonus is missing. Wrap nullable terms in COALESCE(..., 0) to neutralize them. The mirror-image function NULLIF deliberately produces a NULL — most famously to make division by zero safe.

-- Uses the contacts, staff and products tables from the first example.
-- ⚠️ NULL is contagious in arithmetic: anything + NULL = NULL
SELECT
    name,
    base_pay,
    bonus,
    base_pay + bonus AS total_raw,                 -- NULL if bonus is NULL!
    base_pay + COALESCE(bonus, 0) AS total_fixed   -- treat missing bonus as 0
FROM staff;

-- ✅ Expected result:
-- name | base_pay | bonus | total_raw | total_fixed
-- Maya | 5000 | 500 | 5500 | 5500
-- Leo | 4800 | NULL | NULL | 4800
-- Sam | 5200 | 250 | 5450 | 5450

-- Uses the contacts, staff and products tables from the first example.
-- NULLIF(a, b) returns NULL when a = b — great for safe division
SELECT
    name,
    revenue,
    cost,
    revenue / NULLIF(cost, 0) AS margin   -- avoids divide-by-zero error
FROM products;

-- If cost is 0, NULLIF turns it into NULL and the division
-- yields NULL instead of crashing the query.

-- ✅ Expected result:
-- name | revenue | cost | margin
-- Wireless Mouse | 2400 | 800 | 3
-- Desk Lamp | 1500 | 0 | NULL
-- Notebook | 900 | 300 | 3

4. NULLs and Aggregates

Aggregate functions silently ignore NULLs — except COUNT(*), which counts every row. This means AVG(bonus) divides by the number of non-NULL bonuses, which can differ from the row count and surprise you.

-- Uses the contacts, staff and products tables from the first example.
-- Aggregates IGNORE NULLs (except COUNT(*))
SELECT
    COUNT(*)        AS total_rows,     -- counts every row
    COUNT(bonus)    AS bonus_rows,     -- counts only non-NULL bonuses
    AVG(bonus)      AS avg_bonus       -- averages only the non-NULLs
FROM staff;

-- ✅ total_rows = 3 , bonus_rows = 2 , avg_bonus over the 2 present values.

-- ✅ Expected result:
-- total_rows | bonus_rows | avg_bonus
-- 3 | 2 | 375

🧩 Reorder Challenge

Unscramble these lines into a query that lists each staff member and a total pay where a missing bonus counts as 0, skipping rows with no base pay.

-- Uses the contacts, staff and products tables from the first example.
-- SCRAMBLED — arrange into a query that lists each staff
-- member and a total pay that treats a missing bonus as 0.

FROM staff
SELECT name,
base_pay + COALESCE(bonus, 0) AS total_pay
WHERE base_pay IS NOT NULL

Why: the SELECT list defines total_pay, where COALESCE(bonus, 0) stops a missing bonus from poisoning the sum into NULL. Then FROM names the table and WHERE base_pay IS NOT NULL excludes rows with no base pay — using IS NOT NULL rather than != NULL, which would never match.

🧠 Quick Recall

Predict each result, then reveal. Use the contacts table.

0 — always. Comparing to NULL with = yields UNKNOWN, so no row qualifies. You needed email IS NULL (which would return Leo and Sam).

'N/A' — Priya's phone and mobile are both NULL, so COALESCE falls through to the literal default.

SUM = 30, COUNT = 2 — aggregates ignore the NULL entirely. COUNT(*) on the same three rows would be 3.

Common Errors (and the fix)

• Using = NULL or != NULL: both return zero rows. Use IS NULL / IS NOT NULL.
• NULL poisoning a calculation: price + shipping is NULL if shipping is NULL. Wrap nullable terms: price + COALESCE(shipping, 0).
• Confusing NULL with empty string: '' is a real value; NULL is the absence of one. They behave differently in comparisons and aggregates.
• NOT IN with a NULL in the list: can return no rows. Filter NULLs out of the list, or use NOT EXISTS.

Mini-Challenge: Clean Display Email

Build a display_email with no NULLs using COALESCE and a final placeholder. Expected shape is in the comments.

-- Uses the contacts, staff and products tables from the first example.
-- 🎯 MINI-CHALLENGE — uses only this lesson's ideas
-- Build a clean "display_email" column:
--   1. Use the email if present
--   2. Otherwise fall back to the backup_email
--   3. Otherwise show 'unknown@example.com'
-- Show name and display_email.
--
-- ✅ Expected: no NULLs in the display_email column.

-- your query here

🎉 Lesson Complete

• ✅ NULL means "unknown" — never equal to anything, not even NULL
• ✅ Test for it with IS NULL / IS NOT NULL, never =
• ✅ COALESCE returns the first non-NULL value (great for defaults)
• ✅ NULLIF makes division safe and turns sentinels into NULL
• ✅ Aggregates skip NULLs; only COUNT(*) counts every row
• ✅ Next: a checkpoint to combine everything you've learned

Practice quiz

Continue this course

• Previous: CASE Expressions
• Next: Checkpoint: Querying Data — Combine the query clauses in a challenge — then a quiz
• Quick reference: SQL cheat sheet › CASE & NULL Handling
• From the blog: SQL Joins Explained With Examples

Frequently asked questions

Why doesn't col = NULL work?

NULL means 'unknown', and comparing anything to an unknown yields UNKNOWN, never TRUE — even NULL = NULL. That's why you must use IS NULL and IS NOT NULL, which are special operators built to test for the absence of a value.

What does COALESCE actually do?

COALESCE returns the first non-NULL value from a list of arguments, left to right. COALESCE(phone, mobile, 'N/A') gives the phone if present, otherwise the mobile, otherwise the literal 'N/A'. It's the standard way to supply defaults.

Is NULLIF the opposite of COALESCE?

Sort of. NULLIF(a, b) returns NULL when a equals b, and otherwise returns a. It's handy for turning a sentinel value into a real NULL, or for avoiding divide-by-zero: NULLIF(divisor, 0) makes the division yield NULL instead of erroring.

Do aggregate functions count NULLs?

Mostly no. COUNT(col), SUM, AVG, MIN and MAX all ignore NULLs. Only COUNT(*) counts every row regardless. So AVG over a column with NULLs averages just the non-NULL values — sometimes a surprise.

How does NULL behave in GROUP BY and DISTINCT?

For grouping and de-duplication, all NULLs are treated as equal and collapse into a single group or row — even though NULL = NULL is otherwise never true. It's a deliberate exception that makes GROUP BY and DISTINCT usable on nullable columns.

Related lessons

• WHERE Clause