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 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: any comparison with NULL returns NULL (unknown), not TRUE or FALSE. This is called three-value logic.

-- 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. The only correct way to test for a missing value is IS NULL or IS NOT NULL. This is why a WHERE salary = NULL clause will match zero rows even when nulls exist — you must write WHERE salary IS NULL.

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. Aggregate functions ignore NULLs. COUNT(commission) counts only the rows where commission is not null. AVG(commission) divides the sum by the number of non-null rows, not the total row count. This is usually what you want — but it can surprise you when COUNT(*) and COUNT(column) return different numbers.

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.

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. NULLIF is also the standard way to prevent division-by-zero errors: total / NULLIF(count, 0) returns NULL instead of crashing when count is zero.

Try It Yourself

A customer database has optional phone numbers and address fields. Some rows have empty strings instead of proper NULL because of a legacy import. Fix the data in your query and generate a clean report.

CREATE TABLE customers (
    id      INTEGER PRIMARY KEY,
    name    TEXT NOT NULL,
    email   TEXT NOT NULL,
    phone   TEXT,
    city    TEXT,
    country TEXT
);

INSERT INTO customers VALUES
    (1, 'Alice Johnson', 'alice@example.com', '555-1234',  'New York',  'US'),
    (2, 'Bob Smith',     'bob@example.com',   '',          'London',    'UK'),
    (3, 'Carol Lee',     'carol@example.com', NULL,        '',          'CA'),
    (4, 'David Kim',     'david@example.com', '555-9876',  NULL,        'US'),
    (5, 'Eva Rossi',     'eva@example.com',   '',          'Rome',      NULL);

-- Challenge 1: Treat empty string phone numbers as NULL.
-- Show name, and a "contact" column that shows the phone or 'No phone on file'.

-- Challenge 2: Build a location string "City, Country".
-- Use COALESCE to replace NULL city or country with 'Unknown'.

-- Challenge 3: Count how many customers have a real phone number
-- (neither NULL nor empty string).

-- Start here:
SELECT
    name,
    COALESCE(NULLIF(phone, ''), 'No phone on file') AS contact
FROM customers;

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.