COALESCE, NULLIF and IFNULL

Three ways to handle NULL in MySQL 8.4 — pick a fallback, collapse a value to NULL, or default a single expression.

SyntaxCOALESCE(value, ...) | IFNULL(expr1, expr2) | NULLIF(expr1, expr2)

NULL propagates through nearly every expression, so a single missing value can blank out a whole computed column. These three functions are how you stop that, and each one has a distinct job.

COALESCE and IFNULL

COALESCE(value, ...) returns the first non-NULL argument, or NULL if there is none. It takes any number of arguments, so it expresses a fallback chain: nickname, then first name, then email. IFNULL(expr1, expr2) is the MySQL-specific two-argument shorthand — IFNULL(a, b) and COALESCE(a, b) return the same value. COALESCE() is standard SQL and extends to more arguments, so prefer it unless you are matching surrounding code.

NULLIF

NULLIF(expr1, expr2) goes the other way: it returns NULL when expr1 = expr2 is true, and expr1 otherwise. It is defined as exactly CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END. Two uses dominate: NULLIF(divisor, 0) to make a division yield NULL rather than fail, and NULLIF(col, '') to treat empty strings as missing.

What trips people up

These functions only see NULL. An empty string, a zero and a whitespace-only value all pass through COALESCE() untouched. Aggregates need care too: SUM() already ignores NULL inputs but returns NULL when no rows match, so the default belongs outside — COALESCE(SUM(amount), 0).

Example

Example · sql
-- Customer dashboard row: no NULLs reach the template
SELECT
  c.id,
  -- First non-NULL wins; NULLIF turns blank strings into NULL first
  COALESCE(NULLIF(c.nickname, ''), NULLIF(c.full_name, ''), c.email) AS display_name,
  -- IFNULL(a, b) is the two-argument shorthand for COALESCE(a, b)
  IFNULL(c.phone, 'no phone on file')                                AS phone,
  -- SUM ignores NULL rows but returns NULL when the LEFT JOIN found none,
  -- so the default goes outside the aggregate, not inside
  COALESCE(SUM(o.total), 0)                                          AS lifetime_value,
  -- NULLIF guards the divisor: no rows means NULL, not an error
  ROUND(SUM(o.total) / NULLIF(COUNT(o.id), 0), 2)                    AS avg_order_value
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'
GROUP BY c.id, c.nickname, c.full_name, c.email, c.phone;

When to use it

  • Show a fallback label when the preferred name column is NULL or blank
  • Guard a divisor with NULLIF(x, 0) so a rate calculation cannot fail
  • Return 0 instead of NULL from an aggregate over a LEFT JOIN that matched nothing
  • Collapse imported empty strings to NULL so a single IS NULL test finds all missing values

More examples

Rates that survive a zero denominator

The NULLIF/COALESCE pair is the standard idiom for any ratio. It matters more than it looks: a SELECT returns NULL for x/0, but under the MySQL 8.4 default sql_mode the same division inside an INSERT or UPDATE raises an error, so the guard is what keeps a summary-table refresh from aborting.

Example · sql
SELECT
  campaign_id,
  clicks,
  impressions,
  -- 0 impressions gives NULL rather than a division-by-zero condition
  ROUND(100 * clicks / NULLIF(impressions, 0), 2)               AS ctr_pct,
  -- ...and COALESCE turns that NULL into something the report can print
  COALESCE(ROUND(100 * clicks / NULLIF(impressions, 0), 2), 0)  AS ctr_pct_display
FROM campaign_stats
WHERE day >= CURDATE() - INTERVAL 7 DAY;

Normalising blanks to NULL

Empty string and NULL are different values, and mixing them means every query needs two conditions. One UPDATE with NULLIF collapses them so that IS NULL becomes the single test for missing. The second query shows the longhand the function desugars to.

Example · sql
-- An import wrote '' where it meant 'unknown'; a UNIQUE index cannot
-- see those as distinct, but it can ignore multiple NULLs
UPDATE contacts
SET vat_number = NULLIF(TRIM(vat_number), '')
WHERE vat_number IS NOT NULL;

-- NULLIF is defined as exactly this CASE expression
SELECT NULLIF(TRIM(vat_number), '') AS a,
       CASE WHEN TRIM(vat_number) = '' THEN NULL ELSE TRIM(vat_number) END AS b
FROM contacts;

Defaulting an outer join's missing side

A LEFT JOIN with no match still produces one row per product, with every stock column NULL, so COUNT(s.id) is 0 while SUM(s.qty) is NULL. Wrapping the aggregate is the reliable fix: COALESCE inside SUM only works because that one NULL row exists, and it goes back to NULL the moment the group is genuinely empty, as the second query shows. This is the single most common NULL mistake in inventory and reporting queries.

Example · sql
SELECT
  p.id,
  p.name,
  -- An unmatched LEFT JOIN still produces one row, with every s.* column NULL
  COUNT(s.id)                   AS stock_rows,   -- 0: COUNT(expr) skips NULLs
  SUM(s.qty)                    AS raw_sum,      -- NULL when unmatched
  COALESCE(SUM(s.qty), 0)       AS on_hand,      -- 0: the default wraps the aggregate
  SUM(COALESCE(s.qty, 0))       AS also_zero     -- 0 too, but only because a row exists
FROM products p
LEFT JOIN stock s ON s.product_id = p.id
GROUP BY p.id, p.name;

-- When the group is genuinely empty, only the outer default works:
SELECT SUM(qty)              AS raw_sum,     -- NULL: no rows at all
       SUM(COALESCE(qty, 0)) AS still_null,  -- NULL: nothing to sum
       COALESCE(SUM(qty), 0) AS zero         -- 0
FROM stock
WHERE 1 = 0;

Related

sql nullsql casesql left joinsql sum avgsql cast

Discussion

  • Be the first to comment on this lesson.