0Pricing
SQL Academy · Lesson

COALESCE and NULLIF

Provide defaults and avoid divide-by-zero.

Replacing Missing Values

Detecting NULLs is half the battle — often you want to replace them with a sensible default. SQL's COALESCE function does exactly that.

And when you want to create a NULL on purpose (for example to dodge divide-by-zero), NULLIF is the tool. This lesson covers both.

-- Show a placeholder when phone is missing
SELECT name, COALESCE(phone, 'no phone on file') AS phone
FROM customers;

How COALESCE Works

COALESCE takes any number of arguments and returns the first non-NULL one, left to right.

If every argument is NULL, the result is NULL. Think of it as "use this, or that, or finally this fallback".

SELECT
  COALESCE(NULL, NULL, 'third', 'fourth') AS a, -- 'third'
  COALESCE(NULL, 42)                       AS b, -- 42
  COALESCE(NULL, NULL)                     AS c; -- NULL

All lessons in this course

  1. What NULL Really Means
  2. IS NULL and IS NOT NULL
  3. COALESCE and NULLIF
  4. NULLs in Aggregates and Joins
← Back to SQL Academy