Null Handling

Every real database has missing data. A customer who skips the phone number field, an order with no shipping date yet, a product rating that nobody has submitted — all of these become NULL in SQL. Understanding how NULL behaves is one of the most important skills in SQL, because NULL does not behave like zero, an empty string, or any other ordinary value. If you treat it as one, your queries will silently return wrong results.

In this lesson you'll learn what NULL actually means, how to test for it, how to substitute a fallback with COALESCE(), and how to use NULLIF() to turn problematic values into NULL on purpose.

What You'll Learn

By the end of this lesson you will write one report over a customer table riddled with missing values — nulls in some rows, empty strings standing in for nulls in others — and every column will come back with a real value rather than a hole. The skill underneath it is predicting which rows a filter keeps: a query written against nulls without care discards exactly the rows it was meant to find, exits cleanly, and tells you nothing went wrong.

This is the missing-data-guards milestone the warehouse capstone leans on. The capstone's category and customer reports use LEFT JOIN, and every left join manufactures nulls for the rows with no match — so the guards you write here are what keeps a quiet category showing 0 instead of None. You arrive able to group rows and compute aggregates over them (Aggregation and Grouping) and to compute a value across a whole result with a window function (Window Functions); what is new is that a missing value obeys none of the arithmetic or comparison rules the rest of SQL runs on.

What NULL Is — and What It Is Not

NULL means unknown or absent. It is not zero. It is not an empty string. It is the absence of a value. This distinction matters most when you try to compare it: comparing NULL with =, <>, < or > returns NULL (unknown), not TRUE or FALSE. This is called three-value logic. Some operators are deliberately exempt, and they are the ones you reach for: IS NULL and IS NOT NULL always return 1 or 0, never NULL. OR is exempt in one direction too — NULL OR 1 is 1, because the answer is true whatever the unknown turns out to be.

-- Explore NULL behavior in comparisons
CREATE TABLE employees (
    id      INTEGER PRIMARY KEY,
    name    TEXT NOT NULL,
    salary  REAL,
    manager TEXT
);

INSERT INTO employees VALUES
    (1, 'Alice',   95000, 'Carol'),
    (2, 'Bob',     72000, 'Carol'),
    (3, 'Carol',  130000, NULL),
    (4, 'David',   68000, 'Alice'),
    (5, 'Eva',     NULL,  'Alice');

-- NULL = NULL is NOT true — it returns NULL
SELECT
    name,
    salary,
    salary = NULL       AS eq_null,    -- always NULL, not TRUE
    salary IS NULL      AS is_null,    -- correct way to test
    salary IS NOT NULL  AS has_salary
FROM employees;

Notice that salary = NULL always produces NULL, never TRUE. That is a fact about the expression, and the next block asks you to work out what it means for a WHERE clause built from one — which is a separate step, because WHERE has its own rule about what it does with a predicate that is neither true nor false.

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT NOT NULL, manager TEXT);
INSERT INTO employees VALUES (1,'Alice','Carol'),(2,'Bob','Carol'),(3,'Carol',NULL),(4,'David','Alice'),(5,'Eva','Alice');
SELECT id, name FROM employees WHERE manager = NULL ORDER BY id;

NULL in Arithmetic and Aggregates

Any arithmetic operation that involves NULL produces NULL. NULL + 1 is NULL. NULL * 100 is NULL. This means a single missing value can silently propagate through calculations and produce unexpected results.

First, a per-row query shows how NULL propagates through arithmetic. Whenever any operand is missing, the whole expression becomes NULL:

-- Per-row: NULL propagates through arithmetic
CREATE TABLE sales (
    id          INTEGER PRIMARY KEY,
    rep         TEXT NOT NULL,
    base_salary REAL,
    commission  REAL,
    bonus       REAL
);

INSERT INTO sales VALUES
    (1, 'Alice',  60000, 12000, 3000),
    (2, 'Bob',    55000, 8500,  NULL),
    (3, 'Carol',  65000, NULL,  2500),
    (4, 'David',  50000, NULL,  NULL),
    (5, 'Eva',    60000, 9000,  1500);

SELECT
    rep,
    base_salary,
    commission,
    bonus,
    -- NULL in any part makes the whole expression NULL
    base_salary + commission + bonus AS total_pay
FROM sales;

Bob, Carol, and David all get a NULL total_pay — a single missing value poisons the entire calculation. Only Alice and Eva, who have no missing components, produce a number.

Now a separate aggregate query over the same data shows the opposite behavior: aggregate functions quietly skip NULL rows rather than propagating them.

-- Aggregate: functions skip NULLs entirely
CREATE TABLE sales (
    id          INTEGER PRIMARY KEY,
    rep         TEXT NOT NULL,
    base_salary REAL,
    commission  REAL,
    bonus       REAL
);

INSERT INTO sales VALUES
    (1, 'Alice',  60000, 12000, 3000),
    (2, 'Bob',    55000, 8500,  NULL),
    (3, 'Carol',  65000, NULL,  2500),
    (4, 'David',  50000, NULL,  NULL),
    (5, 'Eva',    60000, 9000,  1500);

SELECT
    COUNT(*)          AS total_reps,
    COUNT(commission) AS reps_with_commission,
    AVG(commission)   AS avg_commission,
    SUM(bonus)        AS total_bonus
FROM sales;

Here COUNT(*) reports all 5 reps, but COUNT(commission) is only 3 because it ignores Carol's and David's NULL commissions. AVG(commission) divides by those 3 non-null rows, not by 5, and SUM(bonus) adds only the three non-null bonuses.

Two key rules to internalize here:

1. Arithmetic with NULL gives NULL. base_salary + commission + bonus is NULL whenever any part is missing.
2. An aggregate over a column ignores the rows where that column is null. COUNT(commission) counts only the rows where commission has a value, and AVG(commission) divides by that same number rather than by the total row count. This is usually what you want. What it means for COUNT(*), which is given no column at all, is the subject of the next block.

A third rule follows from the same idea and catches people out in reports: ORDER BY has to put the missing values somewhere. On this lane they sort as though they were smaller than every real value — first under ORDER BY v, last under ORDER BY v DESC. That is a SQLite decision, not a rule of SQL. PostgreSQL does the opposite: its documentation states that "null values sort as if larger than any non-null value", making NULLS LAST the default on an ascending sort (PostgreSQL 16 documentation: Sorting Rows, ORDER BY and the placement of nulls). If the placement matters to your report, say so with ORDER BY v IS NULL, v rather than relying on the engine.

CREATE TABLE ratings (id INTEGER PRIMARY KEY, product TEXT NOT NULL, stars INTEGER);
INSERT INTO ratings VALUES (1,'Mouse',5),(2,'Mouse',NULL),(3,'Mouse',4),(4,'Pad',3),(5,'Pad',NULL),(6,'Pad',NULL);
SELECT product, COUNT(*) AS responses, AVG(stars) AS avg_stars
FROM ratings GROUP BY product ORDER BY product;

Ordering the Clauses That Filter Around a NULL

The two rules above interact with clause order. A WHERE throws away individual rows before any grouping happens; a HAVING tests a group total that only exists after grouping. When the column in play is nullable, getting that order right is what decides whether a group is counted at all. The five lines below are one query over this table:

CREATE TABLE readings (id INTEGER PRIMARY KEY, station TEXT NOT NULL, celsius INTEGER);
INSERT INTO readings VALUES (1,'north',12),(2,'north',NULL),(3,'north',18),(4,'south',20),(5,'south',NULL),(6,'east',7);

Every arrangement below runs against FROM readings, which is already given.

COALESCE: Substituting a Default for NULL

COALESCE(expr1, expr2, ...) returns the first non-null value from its argument list. It is the standard way to replace a missing value with a sensible default. You can pass as many arguments as needed; COALESCE walks through them left to right and returns the first one that isn't NULL.

-- Use COALESCE to handle missing commission and bonus values
CREATE TABLE sales_reps (
    id          INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    region      TEXT,
    commission  REAL,
    bonus       REAL
);

INSERT INTO sales_reps VALUES
    (1, 'Alice',  'North', 12000, 3000),
    (2, 'Bob',    'South', 8500,  NULL),
    (3, 'Carol',  NULL,    NULL,  2500),
    (4, 'David',  'East',  NULL,  NULL),
    (5, 'Eva',    'West',  9000,  1500);

SELECT
    name,
    -- Replace NULL region with a literal default
    COALESCE(region, 'Unassigned')                AS region,
    -- Replace NULL commission with 0 before adding
    COALESCE(commission, 0) + COALESCE(bonus, 0)  AS total_extra,
    -- Chain of fallbacks: prefer commission, then bonus, then 0
    COALESCE(commission, bonus, 0)                AS primary_extra
FROM sales_reps
ORDER BY name;

COALESCE is especially useful in arithmetic. Instead of letting one NULL poison an entire calculation, wrap each nullable column: COALESCE(commission, 0) + COALESCE(bonus, 0) always returns a number. The chained form — COALESCE(commission, bonus, 0) — is handy when you have a priority order: use the first available value, fall back to the next, and ultimately default to zero.

Try two changes to the fence above — open it with its Open in Playground pill, then edit and Run Code — and predict each before you run it. First, drop the guards from the arithmetic: replace COALESCE(commission, 0) + COALESCE(bonus, 0) with plain commission + bonus. Alice and Eva keep their totals (15000.0 and 10500.0); Bob, Carol and David all collapse to None, because each of them is missing at least one operand. That is the poisoning rule made visible in a single column. Second, swap the chained call to COALESCE(bonus, commission, 0). David, who has neither value, still reports 0, and Bob and Carol are unchanged because each has only one value to offer — but Alice goes from 12000.0 to 3000.0 and Eva from 9000.0 to 1500.0. Only the rows that hold both values move, which is the quickest way to see that COALESCE picks one value by priority rather than combining them.

CREATE TABLE ratings (id INTEGER PRIMARY KEY, product TEXT NOT NULL, stars INTEGER);
INSERT INTO ratings VALUES (1,'Mouse',5),(2,'Mouse',NULL),(3,'Mouse',4),(4,'Pad',3),(5,'Pad',NULL),(6,'Pad',NULL);
SELECT product, COUNT(*) AS responses, AVG(stars) AS avg_stars
FROM ratings GROUP BY product ORDER BY product;

6 row(s) affected
product | responses | avg_stars
-------------------------------
Mouse | 2 | 4.5
Pad | 1 | 3.0

NULLIF: Turning Values into NULL on Purpose

NULLIF(expr, value) does the reverse of COALESCE. It returns NULL if expr equals value, otherwise it returns expr unchanged. This is useful when you have a sentinel value — like 0, 'N/A', or 'unknown' — that was used to represent missing data but should be treated as NULL in your queries.

-- Survey results where 0 means "no response" and 'N/A' means unknown category
CREATE TABLE survey (
    id        INTEGER PRIMARY KEY,
    respondent TEXT NOT NULL,
    score     INTEGER,  -- 0 means they skipped the question
    category  TEXT      -- 'N/A' means uncategorized
);

INSERT INTO survey VALUES
    (1, 'Alice', 8,  'Product'),
    (2, 'Bob',   0,  'Service'),
    (3, 'Carol', 7,  'N/A'),
    (4, 'David', 0,  'N/A'),
    (5, 'Eva',   9,  'Product'),
    (6, 'Frank', 6,  'Service');

SELECT
    respondent,
    score,
    -- Treat score of 0 as NULL (skipped)
    NULLIF(score, 0)                     AS real_score,
    category,
    -- Treat 'N/A' category as NULL
    NULLIF(category, 'N/A')              AS real_category,
    -- Average only over genuine responses
    AVG(NULLIF(score, 0)) OVER ()        AS avg_real_score
FROM survey;

Without NULLIF, AVG(score) would include the zeros and report a misleadingly low average. By converting zeros to NULL first, the aggregate ignores them automatically.

The last column uses the window form you met in Window Functions: AVG(...) OVER (), with empty parentheses, computes the average across every row of the result while leaving each row intact — which is why one survey-wide figure can sit beside each respondent's own score instead of collapsing the six rows into one. What is new is only what sits inside it. The window receives NULLIF(score, 0) rather than score, so the skipped responses arrive as nulls and the average skips them, exactly as it would in a GROUP BY aggregate. That is the general shape worth noticing: cleaning a value with NULLIF and averaging it are independent steps, and they compose the same way whichever kind of aggregate you put on the outside.

NULLIF is also the conventional guard against division by zero — total / NULLIF(count, 0) — but be precise about what it buys you here. SQLite does not raise on division by zero: SELECT 10/0 returns None, exit 0, with no error at all (measured on this lane). So on this engine total / NULLIF(count, 0) and plain total / count produce exactly the same None, and the guard changes nothing you can observe. It is worth writing anyway, for two reasons that are about portability rather than about this lane: PostgreSQL defines a division_by_zero condition as a data exception, SQLSTATE 22012 (PostgreSQL 16 documentation: Appendix A, PostgreSQL Error Codes), so the same expression that quietly returns nothing here is an error condition there — and a reader of your SQL can see from the NULLIF that you thought about the zero case, which they cannot see from total / count.

When a NULL Reaches a Subquery

Everything so far has been about a null sitting in a column you are reading. A null inside a subquery's result is worse, because it changes the answer of a test that never mentions nulls at all. NOT IN is where this bites hardest: a single missing value in the list makes the whole test return NULL for every row, so the outer query keeps nothing.

CREATE TABLE staff (id INTEGER PRIMARY KEY, name TEXT NOT NULL, manager_id INTEGER);
INSERT INTO staff VALUES (1,'Ada',NULL),(2,'Bo',1),(3,'Cy',1),(4,'Di',2);
SELECT id, name FROM staff
WHERE id NOT IN (SELECT manager_id FROM staff)
ORDER BY id;

4 row(s) affected
id | name
---------
3 | Cy
4 | Di

Try It Yourself

A customer database has optional phone numbers, cities and spend figures. A legacy import made it worse: some rows carry an empty string where a proper NULL belongs, so the holes come in two shapes and only one of them answers to IS NULL. This is a build task. The starter below runs and produces a report, but it is the wrong report — it selects the raw columns straight through, so the holes show up as None and as blanks. Work the three TODOs so every cell holds a real value.

CREATE TABLE customers (
id      INTEGER PRIMARY KEY,
name    TEXT NOT NULL,
phone   TEXT,
city    TEXT,
spend_cents INTEGER
);
INSERT INTO customers VALUES
(1, 'Alice Johnson', '555-1234', 'New York', 12000),
(2, 'Bob Smith',     '',         'London',   NULL),
(3, 'Carol Lee',     NULL,       '',         4500),
(4, 'David Kim',     '555-9876', NULL,       NULL),
(5, 'Eva Rossi',     '',         'Rome',     9000);
-- TODO 1: contact must show the phone, or 'No phone on file' when there is none.
--         An empty string counts as none: Bob and Eva both have one, Carol has a null.
-- TODO 2: location must show the city, or 'Unknown' when there is none.
--         Carol has an empty string and David has a null; both must read Unknown.
-- TODO 3: spend must be a number for every row, so a missing spend reads 0 rather than None.
SELECT name, phone AS contact, city AS location, spend_cents AS spend
FROM customers
ORDER BY name;

5 row(s) affected
name | contact | location | spend
---------------------------------
Alice Johnson | 555-1234 | New York | 12000
Bob Smith | No phone on file | London | 0
Carol Lee | No phone on file | Unknown | 4500
David Kim | 555-9876 | Unknown | 0
Eva Rossi | No phone on file | Rome | 9000

Before you submit the version that passes, try two variations on it in the block's editor — an unsolved run is never limited, and each one shows you its output beside the expected lines. Predict each before you run it, then put the passing version back and run that last. First, remove the NULLIF from the contact column, leaving COALESCE(phone, 'No phone on file'): Carol still reads No phone on file because her phone is a real null, but Bob and Eva revert to printing nothing at all — the guard that does not know about empty strings cannot repair them. Second, change the final line to ORDER BY spend, name: because a missing spend has already been turned into a 0 by TODO 3, Bob and David sort to the front by value rather than by the null-placement rule, which is a small demonstration that the cleanest way to control where missing rows land is to stop them being missing.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, spend_cents INTEGER);
INSERT INTO customers VALUES (1,'Ada',5000),(2,'Bo',NULL),(3,'Cy',7000);
SELECT name, COALESCE(spend_cents, 0) AS spend FROM customers ORDER BY name;
SELECT name, IFNULL(spend_cents, 0) AS spend FROM customers ORDER BY name;
SELECT name, CASE WHEN spend_cents IS NULL THEN 0 ELSE spend_cents END AS spend FROM customers ORDER BY name;

Milestone: Guarding the Warehouse Reports

The capstone's reports are built on LEFT JOINs, and a left join manufactures nulls by design: every category with no products, every customer with no orders, arrives with a row full of missing values. The report is only correct if each of those holes has been given a deliberate answer.

Key Takeaways

• NULL means unknown — it is not zero, not an empty string, and not FALSE
• Any comparison with NULL using =, <>, <, > returns NULL, not TRUE or FALSE
• Always use IS NULL or IS NOT NULL to test for missing values
• Arithmetic involving NULL always produces NULL — a single missing value propagates through the whole expression
• Aggregate functions (COUNT, SUM, AVG, MIN, MAX) silently skip NULL rows — COUNT(*) and COUNT(column) can return different values
• COALESCE(a, b, c) returns the first non-null argument — use it to substitute defaults and prevent NULL propagation in arithmetic
• NULLIF(expr, value) returns NULL when expr equals value — useful for cleaning sentinel values and preventing division by zero
• Combine NULLIF and COALESCE to convert bad data to NULL first, then substitute a clean default

Pro Tip: When importing data from external sources, run a quick audit before writing production queries: SELECT COUNT(*) - COUNT(column_name) AS null_count FROM table gives you the exact number of nulls in any column without scanning it twice. Do this for every column you plan to filter, join, or aggregate — knowing your null counts upfront prevents subtle bugs and helps you decide where COALESCE defaults are needed.

Next Steps

Now that you can handle missing data confidently, the next lesson introduces set operations — UNION, INTERSECT, and EXCEPT — which let you combine and compare the results of multiple queries in a single statement.