Missing values and NULL

NULL means unknown. It never equals anything, and it's behind most wrong counts.

NULL is how SQL stores a missing or unknown value. It isn’t zero, and it isn’t an empty string. In the sample data, an open deal has no closed_at yet, so that column is NULL.

Use IS NULL, never = NULL

-- Open deals: closed_at has no value yet
SELECT opportunity_id, name
FROM opportunities
WHERE closed_at IS NULL;
 
-- Closed deals
SELECT opportunity_id, name
FROM opportunities
WHERE closed_at IS NOT NULL;

WHERE closed_at = NULL runs without an error and returns zero rows. Comparing anything with NULL, even another NULL, gives unknown, and WHERE only keeps rows where the answer is TRUE.

NULL hides from filters

This query looks like it returns every deal that isn’t from a partner:

SELECT opportunity_id, lead_source
FROM opportunities
WHERE lead_source <> 'Partner';

It silently drops deals where lead_source is NULL, because “is unknown different from Partner?” is unknown, not TRUE. Keep them on purpose:

WHERE lead_source <> 'Partner'
   OR lead_source IS NULL

Replacing NULL with COALESCE

COALESCE returns the first value in its list that isn’t NULL. It’s the standard way to show a label instead of a blank:

SELECT name, COALESCE(lead_source, 'Unknown') AS lead_source
FROM opportunities;

Why this matters: when a number in a report looks wrong, check for NULLs first. It’s the single most common cause.

Try it

Find open opportunities whose expected close_date has already passed. Return name and close_date. Today’s date is CURRENT_DATE. Field guide exercise B5 is a longer version of this.

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson