Filling in and creating NULLs

COALESCE swaps a missing value for one you choose. NULLIF turns a value you don't want into NULL.

Lesson 5 showed that NULL means unknown and hides from filters. These two functions let you control it on purpose: COALESCE replaces a NULL with a value, and NULLIF does the reverse.

COALESCE: the first value that isn’t NULL

COALESCE takes a list and returns the first item that isn’t NULL:

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

15 of the 70 deals have no lead source. They now show Unknown instead of a blank.

The list can be longer than two. The database checks each item left to right and stops at the first real value:

COALESCE(mobile_phone, office_phone, 'No phone')   -- example columns

Three places COALESCE earns its keep

1. Labelling a NULL group

GROUP BY puts every NULL into one group with a blank label, which looks like a mistake in a report. Name it instead:

SELECT COALESCE(lead_source, 'Unknown') AS source,
       COUNT(*) AS deals
FROM opportunities
GROUP BY source;

Give the result a new alias, like source. If you call it lead_source, some databases group by the original column instead of your label. In this case the counts happen to come out the same, but it’s a habit that bites later.

2. Totals that should show 0

SUM over no rows returns NULL, not 0 (lesson 11). For a report cell that should read 0:

SELECT COALESCE(SUM(amount), 0) AS won_revenue
FROM opportunities
WHERE is_won = 1
  AND owner_id = 14;   -- Erin, a new rep with no deals yet

3. Keeping NULL rows in a “not equal” filter

This misses the deals with no lead source, because NULL <> 'Partner' isn’t TRUE:

SELECT COUNT(*) FROM opportunities WHERE lead_source <> 'Partner';                    -- 45

Swap the NULL for an empty string first, and they’re counted:

SELECT COUNT(*) FROM opportunities WHERE COALESCE(lead_source, '') <> 'Partner';      -- 60

That’s the same result as lead_source <> 'Partner' OR lead_source IS NULL from lesson 5, in a shorter form.

NULLIF: turn a value into NULL

NULLIF(a, b) returns NULL when a equals b, and a otherwise. It sounds backwards until you see the two jobs it does.

Stop a divide-by-zero

On most databases, dividing by zero stops the whole query with an error. Dividing by NULL just returns NULL:

SELECT 45 * 1.0 / NULLIF(0, 0);    -- NULL, and the query keeps going

In a real report the bottom number is a count that might be 0:

SELECT owner_id,
       SUM(CASE WHEN is_won = 1 THEN 1 ELSE 0 END) * 100.0
         / NULLIF(COUNT(closed_at), 0) AS win_rate_pct
FROM opportunities
GROUP BY owner_id;

Treat blanks as missing

Data from forms and spreadsheets often stores “nothing” as an empty string '' instead of NULL. COUNT(column) counts those as real values. NULLIF turns them back into NULL so they’re treated as missing:

SELECT NULLIF(TRIM('   '), '');   -- NULL: spaces only, so nothing there

TRIM removes the spaces (lesson 22), then NULLIF turns the empty result into NULL.

Using both together

A common cleanup pattern: blanks become NULL, then NULL becomes a label.

COALESCE(NULLIF(TRIM(lead_source), ''), 'Unknown')

Read it from the inside out: trim the spaces, turn an empty result into NULL, then replace NULL with Unknown.

Common mistakes

Dialect notes

Snowflake notes

DIV0 returns 0, while NULLIF returns NULL. They chart differently: 0 says “measured, and none”, NULL says “nothing to measure yet”.

More in the field guide: COALESCE / NULLIF, NULL in math and Divide-by-zero protection.

Try it

Show the open pipeline for each lead source, with deals that have no lead source grouped as Unknown. Return the label, the number of deals and their total amount, largest total first.

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

Back to the top of the lesson