SQL Academy · Lesson

COALESCE and NULLIF

Provide defaults and avoid divide-by-zero.

COALESCE and NULLIF is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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

Chaining Fallbacks

Because COALESCE accepts many arguments, you can build a chain of preferences.

For example, prefer a mobile number, then a work number, then a home number, and finally a literal message.

SELECT name,
  COALESCE(mobile, work_phone, home_phone, 'unreachable') AS best_contact
FROM contacts;

Default Values in Calculations

NULL propagates through arithmetic, so a single missing value can NULL-out an entire calculation. Wrap the nullable input in COALESCE to substitute a neutral value.

Here a missing discount is treated as 0.

-- Without COALESCE, a NULL discount makes total NULL
SELECT
  price - COALESCE(discount, 0) AS total
FROM line_items;

COALESCE Type Rules

All arguments to COALESCE must share a compatible type. PostgreSQL picks a common type and will error if it can't.

For example, you can't mix a number and a free-text string without casting — the fallback must be a number too.

-- OK: both integers
SELECT COALESCE(score, 0) FROM results;

-- Cast when mixing types
SELECT COALESCE(score::text, 'n/a') FROM results;

Meet NULLIF

NULLIF(a, b) does the reverse of COALESCE in spirit: it returns NULL when a = b, and otherwise returns a.

It's a compact way to turn a specific "sentinel" value into a real NULL.

SELECT
  NULLIF(5, 5)   AS a,  -- NULL (equal)
  NULLIF(5, 9)   AS b,  -- 5    (not equal)
  NULLIF('', '') AS c;  -- NULL (treat empty string as missing)

NULLIF for Divide-by-Zero

The classic use of NULLIF is avoiding division errors. Dividing by 0 raises an error, but dividing by NULL simply yields NULL.

Wrap the divisor in NULLIF(divisor, 0) to turn a crash into a safe NULL.

-- If total_visits is 0, this would error.
-- NULLIF turns the divisor into NULL, giving a NULL ratio instead.
SELECT
  conversions / NULLIF(total_visits, 0) AS conversion_rate
FROM campaigns;

Combining NULLIF and COALESCE

NULLIF and COALESCE pair up nicely. Use NULLIF to convert a sentinel into NULL, then COALESCE to give that NULL a friendly default.

Here an empty-string note becomes the literal text "—".

-- Treat '' as missing, then display a dash
SELECT
  COALESCE(NULLIF(note, ''), '—') AS display_note
FROM tickets;

Safe Average Ratio

Put it together for a robust analytics query: protect against divide-by-zero with NULLIF, then show 0 instead of NULL with COALESCE.

The query never errors and always shows a clean number.

SELECT
  product_id,
  COALESCE(returns / NULLIF(orders, 0), 0) AS return_rate
FROM product_stats;

COALESCE vs IS NULL

Both deal with NULLs but serve different goals:

IS NULL / IS NOT NULL — test for missing values in a condition.
COALESCE — substitute a value in the output.

Reach for COALESCE when you're shaping results, and for IS NULL when you're filtering.

-- Filter (IS NULL)
SELECT * FROM customers WHERE phone IS NULL;

-- Substitute (COALESCE)
SELECT COALESCE(phone, 'unknown') FROM customers;

Putting It Together

A quick reference for this lesson:

COALESCE(a, b, c) → first non-NULL.
NULLIF(a, b) → NULL when equal, else a.
Use NULLIF(divisor, 0) to avoid divide-by-zero.
Use COALESCE to give defaults in calculations and output.

SELECT
  COALESCE(nickname, full_name, 'guest')       AS display_name,
  revenue / NULLIF(orders, 0)                  AS avg_order_value
FROM accounts;

Quick Check

What does NULLIF(amount, 0) return when amount is 0?

Recap

You can now replace NULLs with defaults using COALESCE (first non-NULL wins) and create NULLs deliberately with NULLIF (NULL when two values match).

You saw the divide-by-zero pattern and how to chain both functions for safe, clean output. Next you'll explore how NULLs behave inside COUNT, SUM, and JOINs.

SELECT
  COALESCE(NULLIF(comment, ''), 'no comment') AS comment,
  total / NULLIF(qty, 0)                       AS unit_price
FROM invoices;

Frequently asked questions

Is the “COALESCE and NULLIF” lesson free?

Yes — the full text of “COALESCE and NULLIF” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “COALESCE and NULLIF”?

Provide defaults and avoid divide-by-zero. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “COALESCE and NULLIF” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy