What do COALESCE and NULLIF do? | Interview Question

Q: What do COALESCE and NULLIF do?

**Target Audience:** Beginners who have met NULL and want the two tools that tame it, and candidates who need the engine details. **Difficulty:** Easy **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16. SQL Server and MySQL variants are cited from their documentation. > **How to read this doc:** Each idea is explained in plain language first, then tagged with `📌 Interview term:` so you learn the words an interviewer expects. Every result and error shown was produced by actually running the query; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First You are phoning a friend. You try their mobile; no number saved. You try their work phone; nothing either. In the end you write "no phone" on your list. That is **COALESCE**: "try these in order, take the first one that exists". Now the opposite. A form has an "email" box, and some people typed a few spaces just to get past it

The problem

Question presented to candidate: "Our contacts table has optional nickname and phone columns, and our ad report divides clicks by impressions, which can be zero. Show me how COALESCE and NULLIF help with each, and explain how they relate to each other."

What a strong answer should cover:

COALESCE(a, b, ...) returns the first argument that is not NULL, scanning left to right; NULL only if all are NULL
NULLIF(a, b) returns NULL when a = b, otherwise a; the classic uses are turning a sentinel ('', 0) into NULL and avoiding division by zero
They combine: COALESCE(NULLIF(TRIM(email), ''), 'no email'), and x / NULLIF(y, 0)
Both are standard shorthands for CASE; COALESCE stops evaluating once it finds a value
Engine details: PostgreSQL raises division by zero and requires compatible argument types, SQLite returns NULL for x / 0 and accepts mixed types; vendor variants IFNULL (MySQL, SQLite) and ISNULL (SQL Server) take two arguments and type the result differently

Code / implementation expected: Yes — a fallback chain with COALESCE and a safe ratio with NULLIF.

Schema used by the examples:

coalesce
null

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who have met NULL and want the two tools that tame it, and candidates who need the engine details. Difficulty: Easy Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16. SQL Server and MySQL variants are cited from their documentation.

How to read this doc: Each idea is explained in plain language first, then tagged with 📌 Interview term: so you learn the words an interviewer expects. Every result and error shown was produced by actually running the query; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

You are phoning a friend. You try their mobile; no number saved. You try their work phone; nothing either. In the end you write "no phone" on your list. That is COALESCE: "try these in order, take the first one that exists".

The solution is hidden so you can try first

11 min read · 11 sections

Run the code

Edit the query and run it against the tables for this question.

SQLCOALESCE fallback chains, and NULLIF to treat blank strings as missing

SQLNULLIF(impressions, 0) makes the click-through rate safe, COALESCE picks what to show

SQLCOALESCE changes what an average means: skip unknown budgets or count them as 0

PostgreSQL: NULLIF prevents the division by zero error

SELECT name,
       ROUND(clicks * 100.0 / NULLIF(impressions, 0), 2) AS ctr_pct,
       COALESCE(budget::text, 'not set') AS budget_label
FROM campaigns
ORDER BY id;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.