SQL Quiz: Coalesce And Ifnull
10 questions · exam conditions
0:00
Coalesce And IfnullQuestion 1 of 10

An update API uses UPDATE profiles SET display_name = COALESCE(:new_name, display_name). The API must support both leaving the existing name unchanged and intentionally setting the name to NULL.

Why is the current expression insufficient?

It treats a NULL parameter as "leave unchanged," so the same parameter cannot also request storing NULL.
It always stores NULL when the parameter is NULL, so preserving the old value is impossible.
It updates only rows whose existing display name is NULL, regardless of the supplied parameter.
It converts every non-NULL parameter to the existing display name before performing the update.
← Back to quizzes

SQL Quiz

SQL Quiz: Coalesce And Ifnull

Practice Coalesce And Ifnull in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Coalesce And Ifnull, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

An update API uses UPDATE profiles SET display_name = COALESCE(:new_name, display_name). The API must support both leaving the existing name unchanged and intentionally setting the name to NULL.

Why is the current expression insufficient?

  1. It treats a NULL parameter as "leave unchanged," so the same parameter cannot also request storing NULL. (correct answer)
  2. It always stores NULL when the parameter is NULL, so preserving the old value is impossible.
  3. It updates only rows whose existing display name is NULL, regardless of the supplied parameter.
  4. It converts every non-NULL parameter to the existing display name before performing the update.
Explanation: When you see a question about COALESCE in an UPDATE statement, think carefully about what "NULL as a parameter" means in two different scenarios — they can collide in ways that break your API's contract. COALESCE(:new_name, display_name) returns the first non-NULL value in its list. So if :new_name is NULL, the expression falls back to display_name, effectively leaving the column unchanged. This behavior is exactly what you want when the caller passes nothing — but it's a problem when the caller intentionally passes NULL to erase the name. In that case, the expression silently ignores the intent and preserves the old value instead of storing NULL. A single parameter cannot carry two meanings: "I passed NULL because I want to store NULL" and "I passed NULL because I want no change." That's precisely why A is correct — the NULL parameter is overloaded, making one of the two required behaviors unreachable. B is the mirror-image misconception: it claims the expression always stores NULL when the parameter is NULL, which is the opposite of what COALESCE does. C invents behavior that has nothing to do with COALESCE — the function doesn't filter rows based on existing column values. D misreads the function entirely; COALESCE never replaces a non-NULL input with the existing column value. A practical fix is to use a sentinel pattern — a separate boolean flag like :clear_name — or to handle the two cases in application logic before the SQL runs. Study tip: Whenever COALESCE is used for "skip if null" logic, ask yourself whether NULL also needs to be a valid intended value. If it does, COALESCE alone is insufficient.

Question 2

A database dialect supports IFNULL, but its documented syntax accepts exactly two arguments. An existing expression uses COALESCE(billing_email, account_email, 'none').

Which replacement preserves the original first-non-NULL behavior in that dialect?

  1. IFNULL(billing_email, account_email, 'none')
  2. IFNULL(billing_email, IFNULL(account_email, 'none')) (correct answer)
  3. IFNULL(account_email, IFNULL(billing_email, 'none'))
  4. IFNULL(billing_email, account_email)
Explanation: When working with NULL-handling functions, the key distinction to understand is that COALESCE is designed to accept multiple arguments and returns the first non-NULL value among them, while IFNULL is a two-argument shorthand that returns the first argument if it's not NULL, otherwise returns the second. The challenge here is replicating multi-argument COALESCE behavior using only two-argument IFNULL calls. The original COALESCE(billing_email, account_email, 'none') checks three values in order: if billing_email is not NULL, return it; otherwise try account_email; otherwise return 'none'. To replicate this with IFNULL, you nest the calls — the inner call handles the fallback between account_email and 'none', and the outer call decides whether to use billing_email or defer to that inner result. That's exactly what B does: IFNULL(billing_email, IFNULL(account_email, 'none')) preserves the correct priority order. A simply passes three arguments to IFNULL, which violates the documented two-argument syntax — this will cause an error, not a result. C reverses the priority, checking account_email before billing_email, which changes the logic entirely — if account_email is non-NULL, it would be returned even when billing_email also has a value. D drops 'none' as the final fallback, meaning the expression returns NULL if both emails are NULL instead of the string 'none'. A useful pattern to remember: any COALESCE(a, b, c, ...) can be rewritten as nested IFNULL calls from the inside out — start with the last two arguments and work outward, always preserving left-to-right priority.

Question 3

A team is migrating expressions from a system that uses IFNULL(a, b) to systems that support COALESCE(a, b). The arguments sometimes have different data types, such as a numeric column and a text literal.

Which migration claim is the safest?

  1. The functions always have identical inferred types because both select the first non-NULL value.
  2. Replacing IFNULL with COALESCE always prevents conversion errors by converting both arguments to text.
  3. The NULL-selection intent is similar, but type resolution and implicit conversion rules must be checked for each dialect. (correct answer)
  4. The functions differ only in spelling, so mixed-type arguments require no migration testing.
Explanation: When migrating SQL functions across database systems, the most dangerous assumption you can make is that two functions behaving similarly in one dialect will behave identically in all dialects — especially around type resolution. IFNULL(a, b) and COALESCE(a, b) share the same logical intent: return the first non-NULL value. However, the SQL standard and individual database engines (MySQL, PostgreSQL, SQL Server, SQLite) each define their own rules for how the return type is determined when arguments have mixed types. For example, one dialect might promote a numeric to text, another might throw a type mismatch error, and another might silently truncate. This is why C is the safest claim — it acknowledges the functional similarity while correctly flagging that type resolution and implicit conversion behavior must be verified per dialect before assuming a safe swap. A is wrong because "identical inferred types" is too strong a guarantee. Both functions pick the first non-NULL value, yes, but how they resolve the output type from mixed inputs is dialect-specific, not universal. B is wrong because neither function guarantees conversion to text — that's simply not how either function is specified. This answer invents a behavior that doesn't exist. D is wrong because calling the functions different only in spelling dismisses real implementation differences; mixed-type arguments are exactly where subtle bugs hide, and skipping migration testing is a recipe for silent data corruption or runtime errors. Your study tip: whenever a question involves function equivalence across SQL dialects, treat "always" and "never" claims with suspicion — SQL behavior is highly vendor-specific, especially around type coercion.

Question 4

In a dialect where || concatenation with NULL produces NULL, a report must show every available part of a person's name. It must show '(unknown)' only when both first_name and last_name are NULL.

Which expression satisfies all three cases: one name present, both names present, and both names missing?

  1. COALESCE(TRIM(first_name || ' ' || last_name), '(unknown)')
  2. TRIM(COALESCE(first_name, last_name, '(unknown)'))
  3. COALESCE(TRIM(COALESCE(first_name, '') || ' ' || COALESCE(last_name, '')), '(unknown)')
  4. COALESCE(NULLIF(TRIM(COALESCE(first_name, '') || ' ' || COALESCE(last_name, '')), ''), '(unknown)') (correct answer)
Explanation: When combining nullable name columns, you need to think about three distinct failure modes: NULL propagation during concatenation, leftover whitespace when only one name exists, and the edge case of a result that's technically non-NULL but meaningless (a lone space ' '). The expression in D handles all three systematically. First, COALESCE(first_name, '') and COALESCE(last_name, '') replace NULLs with empty strings, preventing NULL from poisoning the concatenation. Then TRIM(...) cleans up the resulting string — so if only one name exists, you don't get a trailing or leading space. The critical final step is NULLIF(..., ''), which converts a pure whitespace result (when both names were NULL and concatenation produced ' ') into NULL, allowing the outer COALESCE to substitute '(unknown)'. Every case is covered cleanly. A fails immediately because concatenating with || when either name is NULL produces NULL — so a single NULL name causes the entire expression to collapse to '(unknown)', hiding a real name that's present. B only returns whichever name isn't NULL first, completely discarding the other. If both names exist, you'll never see both of them — it picks one and stops. C gets surprisingly close but misses the whitespace trap. When both names are NULL, COALESCE(first_name, '') || ' ' || COALESCE(last_name, '') produces ' ' (a single space). TRIM reduces it to '', but COALESCE doesn't treat an empty string as NULL — so the outer COALESCE never fires, and you see a blank instead of '(unknown)'. The key pattern to remember: whenever you convert NULLs to empty strings for safe concatenation, you must use NULLIF afterward to catch the empty-string edge case before applying your fallback default.

Question 5

A customer row has nickname = '', preferred_name = NULL, and legal_name = 'Ana Ruiz'. In this database, an empty string is distinct from NULL.

What does the expression COALESCE(nickname, preferred_name, legal_name, 'Unknown') return for this row?

  1. 'Ana Ruiz', because COALESCE skips both empty strings and NULL values.
  2. '', because the empty string is the first value that is not NULL. (correct answer)
  3. 'Unknown', because the first two candidate columns contain no usable name.
  4. NULL, because a NULL before legal_name stops evaluation of the expression.
Explanation: When you see COALESCE in SQL, your job is to trace through its arguments one by one and ask a single question at each step: is this value NULL? That's the only test COALESCE performs — it returns the first argument that is not NULL. In this row, nickname = '' (an empty string). Empty strings are real, non-NULL values in SQL — they occupy a position in memory and pass the COALESCE check immediately. So COALESCE(nickname, preferred_name, legal_name, 'Unknown') evaluates the first argument, finds '', determines it is not NULL, and returns '' right there. Evaluation stops; the remaining arguments are never reached. That makes B the correct answer. A is wrong because it describes behavior that doesn't exist. COALESCE has no concept of "usable" or "meaningful" — it only checks for NULL. It will happily return an empty string, a zero, or any other non-NULL value, no matter how semantically empty it seems. C is wrong for the same reason. Skipping an empty string as "unusable" would require custom logic (like NULLIF(nickname, '')) that simply isn't present in this expression. D is wrong because NULL does not "stop" evaluation — it's the opposite. NULL causes COALESCE to continue to the next argument. Only a non-NULL value stops the search. Study tip: Burn this in: COALESCE = first non-NULL. If you want to also skip empty strings, you need COALESCE(NULLIF(column, ''), ...) — that's a common real-world pattern and a likely exam trap.

Question 6

A query uses WHERE product_code = COALESCE(:requested_code, product_code) so that a NULL parameter is intended to mean "do not filter." Some rows have product_code = NULL.

What is the flaw, and which predicate correctly implements the intended behavior?

  1. The original includes NULL product codes; use product_code = :requested_code to exclude them.
  2. The original fails only for non-NULL parameters; use product_code IS NULL OR product_code = :requested_code.
  3. The original includes every row for any parameter; use COALESCE(product_code, :requested_code) = product_code.
  4. The original excludes NULL product codes when the parameter is NULL; use :requested_code IS NULL OR product_code = :requested_code. (correct answer)
Explanation: Whenever you see COALESCE used to create an "optional filter," you need to trace through what happens when the parameter is NULL and when column values are NULL — these are two separate cases that both require attention. The intended logic is: "If the parameter is NULL, return all rows; otherwise, return only rows matching the parameter." The original predicate product_code = COALESCE(:requested_code, product_code) tries to achieve this by substituting product_code for itself when the parameter is NULL, turning the condition into product_code = product_code. This sounds clever, but here's the trap: in SQL, NULL = NULL evaluates to unknown (not TRUE), so any row where product_code IS NULL will be silently excluded, even when the parameter is NULL and the intent is to return everything. That's the flaw — D correctly identifies it and offers the fix: :requested_code IS NULL OR product_code = :requested_code. When the parameter is NULL, the first condition is TRUE for every row (including NULL-coded rows), and when it isn't NULL, the equality filter applies normally. A misunderstands the problem entirely — the goal isn't to exclude NULL product codes, it's to include them when doing a "show all" query. B incorrectly claims the predicate works for non-NULL parameters; in reality, the flaw exists specifically when the parameter is NULL (for rows with NULL codes). C is completely wrong — COALESCE(product_code, :requested_code) = product_code doesn't implement the intended logic and would fail in multiple scenarios. The key study tip: remember that NULL = NULL is never TRUE in SQL. Any predicate that relies on self-equality to "pass through" NULL values will silently drop NULL rows — always use explicit IS NULL checks instead.

Question 7

Three tasks have priority values 2, 999, and NULL. A query orders them with ORDER BY COALESCE(priority, 999) ASC, and no secondary sort expression is present.

Which ordering conclusion is guaranteed?

  1. Priority 2 appears first, while the relative order of priority 999 and NULL is not guaranteed. (correct answer)
  2. Priority 2 appears first, then priority 999, and the NULL priority always appears last.
  3. The NULL priority appears first because COALESCE preserves the database's default NULL ordering.
  4. Priority 999 and NULL are removed from the result because both map to the fallback value.
Explanation: When you see COALESCE inside an ORDER BY, think carefully about what it does — and what it doesn't do. COALESCE(priority, 999) replaces NULL with 999 purely for the purpose of computing the sort key. It doesn't change stored values, and crucially, it doesn't distinguish between a row that actually has priority 999 and a row whose NULL was substituted with 999. That's exactly why A is correct. After COALESCE is applied, the row with priority 2 gets sort key 2, while both the row with priority 999 and the NULL row get sort key 999. Since 2 < 999, the priority-2 row is guaranteed first. But the two rows tied at sort key 999 have no secondary sort expression to break the tie, so their relative order is non-deterministic — the database can return them in any sequence. B is wrong because it assumes the NULL row will always appear after the genuine 999 row. There's nothing in the query to enforce that; both rows are indistinguishable to the sort engine. C is wrong on two levels: COALESCE does the opposite of preserving NULL ordering — it actively replaces NULL with a fallback value. There is no "default NULL ordering" that COALESCE passes through. D is wrong because COALESCE is a scalar function that transforms values for computation; it never filters or removes rows from results. A good rule of thumb: whenever two rows produce the same sort key with no tiebreaker, their order is undefined. Always ask yourself whether your ORDER BY is fully deterministic.

Question 8

An invoice line has quantity, unit_price, and discount. A missing discount means zero discount, but a missing quantity or unit price means that the line total is unknown.

Which expression implements those requirements correctly?

  1. COALESCE(quantity * unit_price - discount, 0)
  2. COALESCE(quantity, 0) * unit_price - discount
  3. quantity * unit_price - COALESCE(discount, 0) (correct answer)
  4. COALESCE(quantity * unit_price, discount, 0)
Explanation: When working with NULL values in SQL, the key question to ask is: which nulls should be treated as zero, and which nulls signal that a result is genuinely unknown? COALESCE replaces a NULL with a fallback, so you must apply it precisely — only where a NULL should be substituted, not where it should propagate. Here, the problem tells you exactly how to handle each column: a missing discount is treated as zero, but a missing quantity or unit_price means the total is unknowable and should return NULL. Option C, quantity * unit_price - COALESCE(discount, 0), does exactly this. If either quantity or unit_price is NULL, the multiplication returns NULL and that propagates as the final result. Meanwhile, COALESCE(discount, 0) safely substitutes zero when discount is missing. Option A wraps the entire expression in COALESCE(..., 0), which means a line with unknown quantity or unit price would silently return 0 — masking the fact that the total is actually unknown. That's a data integrity problem. Option B applies COALESCE(quantity, 0), treating a missing quantity as zero units — but zero units times any price is $0, which is a false total, not an unknown one. It also leaves discount unprotected from NULL. Option D is a misuse of COALESCE entirely: it would return the raw discount value if quantity * unit_price is NULL, which makes no semantic sense as a line total. The study tip here: before reaching for COALESCE, identify what each NULL means in context. Apply the substitution only where a NULL has a meaningful default — never just to avoid seeing NULL in your output.

Question 9

An orders query left-joins payments and displays COALESCE(p.amount, 0) AS paid_amount. A payment row may exist with amount = NULL, and some orders have no payment row at all.

Which statement correctly describes what the displayed value can establish?

  1. A displayed zero proves that no matching payment row exists for the order.
  2. A displayed zero proves that a payment row exists with either zero or NULL amount.
  3. A displayed zero cannot distinguish no payment row from a matched row with zero or NULL amount. (correct answer)
  4. A displayed NULL distinguishes an unmatched order from a matched payment with zero amount.
Explanation: When working with COALESCE and LEFT JOIN together, you need to think carefully about information loss — specifically, whether a transformed output value can be traced back to a unique cause. COALESCE(p.amount, 0) replaces any NULL with 0. Here's the problem: a zero in the output can come from three distinct situations: the order had no matching payment row (so p.amount is NULL due to the left join), the order had a matching payment row with amount = NULL, or the order had a matching payment row with amount = 0. Since all three scenarios collapse into the same displayed value of 0, you simply cannot distinguish between them. That's exactly what C captures — a displayed zero is ambiguous across these cases. A is wrong because it overclaims certainty. Seeing 0 does not prove the absence of a payment row; a row could exist with a NULL or zero amount. B is also wrong, but for the opposite reason — it excludes the "no row at all" case. A displayed zero could still mean there's no payment row, not just a row with a zero or NULL amount. D is wrong because a displayed NULL cannot appear here at all. COALESCE guarantees that if p.amount is NULL (for any reason), the result is 0, never NULL. So the scenario D describes is impossible given this query. As a study tip: whenever you see COALESCE wrapping a column in a LEFT JOIN, ask yourself how many source states map to each possible output — if multiple causes produce the same value, that output is ambiguous.

Question 10

A report must average the recorded test scores while ignoring missing scores. It should display zero only when no non-NULL scores exist.

Which expression best implements the requirement?

  1. AVG(COALESCE(score, 0))
  2. COALESCE(AVG(score), 0) (correct answer)
  3. AVG(score) + COALESCE(score, 0)
  4. COALESCE(SUM(score), 0) / COUNT(*)
Explanation: When working with aggregate functions and NULL handling in SQL, the key question to ask is: where should the NULL substitution happen — before or after aggregation? AVG() in SQL already ignores NULL values by design. It sums only the non-NULL values and divides by the count of non-NULL rows. The requirement asks you to preserve this behavior but return 0 when no non-NULL scores exist at all (i.e., when AVG would return NULL because every row is NULL or the table is empty). That's exactly what B — COALESCE(AVG(score), 0) — does: it lets AVG work naturally across non-NULL scores, then wraps the final result so a NULL aggregate becomes 0. A — AVG(COALESCE(score, 0)) — looks reasonable but is subtly wrong. By replacing NULLs with 0 before averaging, you drag those zero substitutions into the calculation, artificially pulling the average down. A student with no score would count as scoring zero, which violates the "ignore missing scores" requirement. C — AVG(score) + COALESCE(score, 0) — mixes an aggregate function with a non-aggregated column reference in an invalid way. score outside an aggregate is meaningless in this context and would cause an error or undefined behavior. D — COALESCE(SUM(score), 0) / COUNT(*) — divides by the count of all rows including NULLs, not just rows with scores. This produces an incorrect (lower) average whenever NULL scores exist. A useful rule of thumb: wrap the aggregate in COALESCE, not the input to it, when you want to preserve SQL's built-in NULL-ignoring behavior.