Questions: COALESCE and NULLIF: NULL Handling Functions

Question 1

A query calculates revenue per unit with the expression `revenue / units_sold`. When units_sold is 0, the query crashes. Which expression fixes this while preserving NULL to signal the undefined result?

• A. CASE WHEN units_sold = 0 THEN 0 ELSE revenue / units_sold END
• B. revenue / NULLIF(units_sold, 0)
• C. COALESCE(revenue / units_sold, 0)
• D. ISNULL(units_sold, 1)

Answer: (B)

NULLIF(units_sold, 0) converts zero to NULL, making the division result NULL rather than causing a divide-by-zero error. This is the idiomatic use case for NULLIF. Option A uses 0 as the fallback, which is semantically wrong — dividing by zero is undefined, not zero. Option C uses COALESCE around the division, but the division still crashes before COALESCE can catch anything. Option D uses ISNULL which is database-specific and only handles NULL, not zero.

Question 2

A customer table has three phone columns: mobile_phone, home_phone, work_phone. Many customers have only one or two. You want a single 'best contact number' column returning whichever is available, or 'No contact' if all are NULL. Which expression is correct?

• A. ISNULL(mobile_phone, home_phone)
• B. COALESCE(mobile_phone, home_phone, work_phone, 'No contact')
• C. NULLIF(mobile_phone, home_phone)
• D. CASE WHEN mobile_phone IS NOT NULL THEN mobile_phone WHEN home_phone IS NOT NULL THEN home_phone ELSE work_phone END

Answer: (B)

COALESCE accepts any number of arguments and returns the first non-NULL value. Option B elegantly handles all three columns plus a literal fallback in one expression. Option A only checks two columns. Option C uses NULLIF, which does something entirely different (returning NULL when two values are equal). Option D is a valid CASE statement but verbose, and it drops work_phone in the final ELSE — illustrating why COALESCE is cleaner for fallback chains.

Question 3

COALESCE(a, b) and NULLIF(a, b) are inverses: COALESCE handles the case where a IS NULL, and NULLIF handles the case where a IS NOT NULL.

• T. True
• F. False

Answer: False

This is a seductive framing, but the functions are not inverses and don't work symmetrically. COALESCE(a, b) returns b when a is NULL and a otherwise — it handles a missing-value fallback. NULLIF(a, b) returns NULL when a EQUALS b (not when a is NULL) and returns a otherwise — it converts a specific value to NULL. The natural pairing is: use NULLIF to normalize bad data into proper NULLs, then use COALESCE to substitute defaults where NULLs appear. They complement each other in a pipeline, not as strict inverses.

Question 4

COALESCE can accept more than two arguments and evaluates them in order, returning the first non-NULL value found.

• T. True
• F. False

Answer: True

Unlike ISNULL or NVL (which are database-specific and accept exactly two arguments), COALESCE is SQL-standard and accepts an arbitrary number of arguments. It evaluates each in sequence and returns the first non-NULL. This makes it more versatile: COALESCE(col1, col2, col3, 'default') handles a multi-column fallback chain without nested function calls. The sequential evaluation also means you can combine columns and literals in any order.

Question 5

Explain how COALESCE and NULLIF work together in data cleaning pipelines, giving a concrete example.

Model answer: NULLIF normalizes sentinel or placeholder values into proper NULLs; COALESCE then substitutes meaningful defaults where NULLs appear. For example: COALESCE(NULLIF(middle_name, ''), 'N/A') first converts empty strings to NULL (because some systems store missing names as '' rather than NULL), then replaces the NULL with 'N/A' for display. Without NULLIF, an empty string would satisfy COALESCE and appear as '' in results. Without COALESCE, the NULL from NULLIF would propagate. Together they give precise control over missing data.

The workflow is: (1) use NULLIF to convert dirty or sentinel values into proper NULLs so aggregate functions and comparisons handle them consistently; (2) use COALESCE at the output stage to substitute user-facing defaults. This separates concerns cleanly: normalization happens internally, presentation happens externally. The alternative — nested CASE WHEN expressions — achieves the same result but is far more verbose and harder to read at a glance.