COALESCE, NULLIF and ISNULL
Substituting defaults and the difference between COALESCE and vendor-specific functions.
COALESCE, NULLIF and ISNULL is a free SQL Interview Prep 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 Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
Substituting Values for NULL
Now that you can detect NULL, the next interview skill is replacing it with a sensible default. The portable, standard tool for this is COALESCE.
Alongside it you will meet NULLIF, which goes the other direction by turning a specific value into NULL, and the vendor functions ISNULL (SQL Server) and IFNULL (MySQL) that candidates often confuse with COALESCE.
Knowing exactly how each differs, especially in argument count and return type, is a frequent screening question.
COALESCE Basics
COALESCE takes any number of arguments and returns the first non-NULL one, scanning left to right. If all arguments are NULL, it returns NULL.
It is ANSI standard and works on every major database, which is why it should be your default answer. Use it to supply fallbacks for display, calculation, or grouping.
-- Show 0 instead of NULL for missing bonuses
SELECT name, COALESCE(bonus, 0) AS bonus
FROM employees;
-- Multiple fallbacks, first non-NULL wins
SELECT COALESCE(mobile_phone, home_phone, 'no phone') AS contact
FROM customers;COALESCE Is Short-Circuiting
A nuance interviewers probe: COALESCE conceptually evaluates arguments left to right and stops at the first non-NULL. So a later, expensive expression is not needed once an earlier one resolves.
In practice optimizers may still evaluate eagerly in some engines, so do not rely on it to guard against errors like division by zero. But the left-to-right precedence of which value wins is guaranteed.
-- Prefer the manual override, else the computed value,
-- else a constant default
SELECT COALESCE(manual_price, list_price * 1.1, 9.99) AS price
FROM products;COALESCE and Result Data Type
A subtle gotcha: the data type of a COALESCE result is determined by the type precedence of all its arguments combined, not just the first one. Mixing incompatible types can cause errors or unexpected truncation.
For example, COALESCE over an integer column and a string default may fail or implicitly cast, depending on the engine. Interviewers use this to test whether you think about types.
-- Risky: integer column with a string fallback
-- may error or force a cast depending on dialect
SELECT COALESCE(score, 'N/A') FROM tests;
-- Safer: keep the fallback type-compatible, or cast explicitly
SELECT COALESCE(CAST(score AS VARCHAR), 'N/A') FROM tests;ISNULL (SQL Server) vs COALESCE
SQL Server has ISNULL(expr, replacement). It looks like COALESCE but differs in important ways interviewers love to contrast:
- Argument count: ISNULL takes exactly two; COALESCE takes many.
- Return type: ISNULL uses the type of the first argument, which can truncate the replacement. COALESCE uses combined type precedence.
- Portability: ISNULL is SQL Server only; COALESCE is ANSI standard.
Recommendation to state aloud: prefer COALESCE for portability and predictable typing.
-- SQL Server: ISNULL may truncate the replacement to
-- the first argument's type (e.g. CHAR(1))
SELECT ISNULL(code, 'UNKNOWN') FROM items;
-- If code is CHAR(1), 'UNKNOWN' becomes 'U'
-- COALESCE picks the wider type and keeps 'UNKNOWN'
SELECT COALESCE(code, 'UNKNOWN') FROM items;IFNULL and NVL
Other dialects have their own two-argument shorthands:
- MySQL / SQLite:
IFNULL(expr, replacement) - Oracle:
NVL(expr, replacement), plusNVL2for a then/else twist
All three behave like a two-argument COALESCE. If asked for the MySQL or Oracle idiom specifically, name these; otherwise reach for COALESCE.
-- MySQL
SELECT IFNULL(bonus, 0) FROM employees;
-- Oracle
SELECT NVL(bonus, 0) FROM employees;
-- NVL2(bonus, 'has bonus', 'no bonus') -> if/else on NULLNULLIF: The Opposite Direction
NULLIF(a, b) returns NULL when a = b, otherwise it returns a. It deliberately creates a NULL, which is the reverse of COALESCE.
Its most famous use is guarding against division by zero. Wrap the denominator in NULLIF(denominator, 0): if it is zero, the divisor becomes NULL and the whole division returns NULL instead of throwing an error.
-- Avoid divide-by-zero: returns NULL instead of erroring
SELECT revenue / NULLIF(orders, 0) AS avg_order_value
FROM daily_stats;
-- NULLIF(5, 5) -> NULL
-- NULLIF(5, 3) -> 5Combining NULLIF and COALESCE
The two pair beautifully. A canonical interview one-liner is 'safe division that shows 0 when there are no orders'. Use NULLIF to dodge the error, then COALESCE to replace the resulting NULL.
This compact idiom signals fluency: you handle the edge case and the presentation in one expression.
SELECT
COALESCE(revenue / NULLIF(orders, 0), 0) AS avg_order_value
FROM daily_stats;
-- orders = 0 -> NULLIF gives NULL -> division gives NULL
-- -> COALESCE turns it into 0Treating Empty Strings as NULL
Another practical NULLIF use: collapsing blank strings to NULL so they can be coalesced uniformly. Dirty data often mixes NULL and ''; this normalizes both.
Read the pattern as: 'if the value is empty, make it NULL, then fall back to a default.' It is a clean, portable answer to 'how do you treat blanks and missing values the same way?'
-- Treat both '' and NULL as missing, default to 'Anonymous'
SELECT COALESCE(NULLIF(TRIM(username), ''), 'Anonymous')
FROM users;Deeper Example: Coalescing Across Joins
After a LEFT JOIN, unmatched rows produce NULLs on the right side. COALESCE turns those into meaningful defaults in the output, a very common reporting requirement.
Here, customers with no orders still appear (thanks to LEFT JOIN), and their total shows as 0 rather than NULL. Mentioning that the COALESCE happens after the join, not inside it, shows you understand evaluation order.
SELECT
c.name,
COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;
-- Customers with no orders get 0 instead of NULLInterview Talking Points
Summary of the substitution toolkit:
- COALESCE(a, b, ...): first non-NULL, multi-arg, ANSI standard, type by precedence. Default choice.
- ISNULL / IFNULL / NVL: two-arg vendor shorthands; ISNULL can truncate to the first arg's type.
- NULLIF(a, b): returns NULL when equal; great for divide-by-zero guards and normalizing blanks.
- Combine
COALESCE(x / NULLIF(y, 0), 0)for safe, presentable division.
Lead with COALESCE and mention vendor variants only when the dialect is fixed.
Quick Check
Choose the safe division expression.
Recap
You can now substitute and manufacture NULLs:
- COALESCE returns the first non-NULL of many arguments; it is the portable default.
- ISNULL (SQL Server), IFNULL (MySQL), and NVL (Oracle) are two-arg shorthands; ISNULL can truncate to the first argument's type.
- NULLIF(a, b) returns NULL when the two are equal, ideal for divide-by-zero guards and blank-string normalization.
- Combine them for safe, presentable expressions and to default post-LEFT-JOIN NULLs.
Final lesson: how NULL behaves inside aggregates, joins, and DISTINCT.
Frequently asked questions
Is the “COALESCE, NULLIF and ISNULL” lesson free?
Yes — the full text of “COALESCE, NULLIF and ISNULL” is free to read here on the web, and the SQL Interview Prep 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 Interview Prep course, upgrade to CoddyKit PRO.
What will I learn in “COALESCE, NULLIF and ISNULL”?
Substituting defaults and the difference between COALESCE and vendor-specific functions. You practise SQL Interview Prep 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 Interview Prep?
No prior experience is required. SQL Interview Prep 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, NULLIF and ISNULL” 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 Interview Prep lesson?
Yes. Every SQL Interview Prep 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.