How Do COALESCE and NULLIF Work in SQL?
Learn how COALESCE and NULLIF work in SQL, with examples for default values, NULL handling and avoiding divide-by-zero errors, plus interview questions.
Expected Interview Answer
COALESCE returns the first non-NULL value from a list of arguments, and NULLIF returns NULL when its two arguments are equal (otherwise the first argument). Together they let you substitute defaults for missing data and convert unwanted values into NULL.
COALESCE(a, b, c) evaluates left to right and stops at the first argument that is not NULL, making it ideal for supplying fallbacks like COALESCE(nickname, first_name, 'Guest'). NULLIF(a, b) is shorthand for a CASE expression that yields NULL if a = b, commonly used to guard against divide-by-zero as in value / NULLIF(divisor, 0). Both are ANSI SQL standard functions and work across major databases.
- Provides clean default values for missing data
- Avoids verbose CASE expressions
- Prevents divide-by-zero errors with NULLIF
- Portable ANSI SQL across databases
- Improves readability of NULL-handling logic
AI Mentor Explanation
Picture a captain naming the next batter: try the specialist first, but if he is injured (NULL) move to the all-rounder, and if he too is unavailable send the nightwatchman. COALESCE walks that batting order and picks the first player actually available. NULLIF is like ruling a run void when two umpires signal the exact same wrong call, cancelling it to a blank.
Step-by-Step Explanation
Step 1
Understand NULL
NULL means unknown or missing; it is not zero or an empty string and any comparison to it yields unknown.
Step 2
Apply COALESCE
Pass a prioritized list of expressions; the function returns the first one that is not NULL.
Step 3
Provide a final default
Make the last COALESCE argument a literal so the result is never NULL when a fallback is required.
Step 4
Apply NULLIF
Pass two expressions; if they are equal you get NULL, otherwise the first expression is returned unchanged.
Step 5
Combine them
Nest NULLIF inside a division and wrap with COALESCE to both avoid divide-by-zero and supply a display default.
What Interviewer Expects
- Correct definition of both functions
- Understanding that COALESCE short-circuits at the first non-NULL
- Knowing NULLIF returns NULL only when arguments are equal
- The divide-by-zero guard pattern
- Awareness that both are ANSI standard and portable
Common Mistakes
- Thinking COALESCE returns the last non-NULL instead of the first
- Confusing NULLIF with IFNULL or ISNULL
- Believing NULL equals zero or an empty string
- Forgetting to add a final literal default in COALESCE
- Assuming NULLIF returns the second argument when values differ
Best Answer (HR Friendly)
“COALESCE looks through a list of values and hands back the first one that actually has data, which is great for filling in defaults. NULLIF does the opposite job: it turns a value into blank when it matches something you want to ignore, most often to stop a divide-by-zero error.”
Code Example
-- Show a display name, falling back through options
SELECT
employee_id,
COALESCE(nickname, first_name, 'Unknown') AS display_name
FROM employees;
-- Avoid divide-by-zero: NULLIF makes the divisor NULL when it is 0
SELECT
product_id,
total_revenue / NULLIF(units_sold, 0) AS revenue_per_unit
FROM sales;
-- Combine both: safe ratio with a readable fallback
SELECT
region,
COALESCE(profit / NULLIF(revenue, 0), 0) AS margin
FROM finances;Follow-up Questions
- How does COALESCE differ from ISNULL or IFNULL?
- Is COALESCE evaluated left to right and does it short-circuit?
- How would you prevent a divide-by-zero error in SQL?
- What data type does COALESCE return when arguments differ?
- Can NULLIF take more than two arguments?
MCQ Practice
1. What does COALESCE(NULL, NULL, 'x', 'y') return?
COALESCE returns the first non-NULL argument scanning left to right, which is 'x'.
2. What does NULLIF(10, 10) return?
NULLIF returns NULL when its two arguments are equal; otherwise it returns the first argument.
3. Why is NULLIF used in an expression like value / NULLIF(divisor, 0)?
When divisor is 0, NULLIF makes it NULL, so the division yields NULL instead of raising a divide-by-zero error.
Flash Cards
What does COALESCE do? — Returns the first non-NULL value from its list of arguments, evaluated left to right.
What does NULLIF(a, b) return? — NULL if a equals b, otherwise a.
Common NULLIF use case? — Guarding division: value / NULLIF(divisor, 0) avoids a divide-by-zero error.
Are COALESCE and NULLIF standard SQL? — Yes, both are ANSI SQL standard functions supported across major databases.