SQL COALESCE: Syntax, Engine Differences, and the NULL Traps Interviewers Test

COALESCE(arg1, arg2, ..., argN) returns the first argument that is not NULL, or NULL if every argument is. It is ANSI-standard, accepts two or more arguments, and runs unchanged on PostgreSQL, MySQL, SQL Server, Oracle, SQLite, BigQuery, and Snowflake. That is the entire syntax.

The syntax is not why interviewers keep writing questions around it. COALESCE sits at the exact point where three-valued logic meets real data, and every call to it encodes a claim about what NULL means in that column. Get the claim wrong and the query runs fine while producing a number that is quietly false. This page covers the function, the ISNULL/IFNULL/NVL engine matrix, and the traps that show up in real SQL screens at Meta, TikTok, Capital One, Robinhood, and Amazon, with runnable examples for each. All queries below use PostgreSQL; engine differences are called out where they exist.

Key Takeaways

• COALESCE(a, b, ...) returns the first non-NULL argument. It takes two or more arguments and is the only NULL-defaulting function that is portable across every major engine.
• The standard defines COALESCE as shorthand for a CASE expression: arguments are evaluated left to right and later ones are skipped, but result types are resolved from all arguments before anything executes.
• COALESCE(x, 0) inside an aggregate changes the math; wrapped around the aggregate it only changes the empty-group result. Decide first whether NULL means "zero" or "unknown" — they demand opposite treatments.
• Wrapping an indexed column in COALESCE inside WHERE or ON blocks index seeks. Rewrite with an explicit IS NULL branch or a UNION ALL.
• Reach for ISNULL/IFNULL/NVL only when a codebase convention forces it. In SQL Server, ISNULL types its result from the first argument and can truncate a string that COALESCE returns intact.

The core move: first non-NULL wins

Asked at TikTok — Select max-discount product per category Given a product catalog where sale_price can be NULL, return exactly one product per category: the one with the largest absolute discount, breaking ties on the smallest product_id. The discount is defined so that a NULL sale price means the product is simply not on sale, and the discount can never go below zero.

The entire problem turns on one decision: what is the effective price of a product whose sale_price is NULL? Subtracting a NULL propagates: list_price - NULL is NULL, and that row silently drops out of any comparison. The fix is to fall back to list_price:

CREATE TABLE products (
  product_id  INT PRIMARY KEY,
  category    TEXT,
  list_price  NUMERIC(10,2),
  sale_price  NUMERIC(10,2)
);

INSERT INTO products VALUES
  (1, 'audio', 120.00,  89.00),
  (2, 'audio',  60.00,  NULL),
  (3, 'video', 300.00, 240.00),
  (4, 'video', 150.00,  NULL);

SELECT product_id,
       sale_price,
       COALESCE(sale_price, list_price) AS effective_price
FROM products
ORDER BY product_id;

 product_id | sale_price | effective_price
------------+------------+-----------------
          1 |      89.00 |           89.00
          2 |       NULL |           60.00
          3 |     240.00 |          240.00
          4 |       NULL |          150.00

(NULL is written out in these result sets; psql shows an empty cell by default.)

From there the full answer is a discount expression plus a window function — ROW_NUMBER partitioned by category handles the "exactly one per category" requirement:

SELECT category, product_id, list_price, sale_price, discount_amount
FROM (
  SELECT p.*,
         GREATEST(list_price - COALESCE(sale_price, list_price), 0) AS discount_amount,
         ROW_NUMBER() OVER (
           PARTITION BY category
           ORDER BY GREATEST(list_price - COALESCE(sale_price, list_price), 0) DESC,
                    product_id
         ) AS rn
  FROM products p
) ranked
WHERE rn = 1
ORDER BY category;

 category | product_id | list_price | sale_price | discount_amount
----------+------------+------------+------------+-----------------
 audio    |          1 |     120.00 |      89.00 |           31.00
 video    |          3 |     300.00 |     240.00 |           60.00

Note what COALESCE did here: it encoded the business rule "NULL sale price means not on sale." The interviewer wrote that rule into the prompt. In production nobody writes it down, and the candidate who asks "does NULL mean no sale, or does it mean the price failed to load?" before typing COALESCE is the candidate who passes. Those are different answers: the first defaults to list_price, the second should probably exclude the row.

COALESCE is a CASE expression wearing a shorter name

Asked at Meta — Write SQL to analyze group-call concurrency Given calls and a participants table where leave_ts is nullable, compute group-call metrics such as peak concurrency. The nullable leave_ts is the crux, and the sensible inference, stated out loud before writing the query, is that a participant with no leave timestamp stayed until the call itself ended, so their presence gets capped at the call's end.

The natural move under that reading is COALESCE(p.leave_ts, c.end_ts). To reason about what that costs and when it runs, you need to know what COALESCE actually is. The SQL standard defines it as pure syntactic shorthand:

COALESCE(a, b)
-- is defined as
CASE WHEN a IS NOT NULL THEN a ELSE b END

Two consequences follow, and both come up as interview follow-ups.

Evaluation short-circuits. Arguments are checked left to right, and once one is non-NULL the rest never execute. You can prove it with an expression that would otherwise blow up:

SELECT COALESCE(1, 1/0) AS survives;

 survives
----------
        1

The 1/0 never runs. Swap the order, COALESCE(NULL, 1/0), and PostgreSQL raises ERROR: division by zero. This matters when the fallback argument is expensive (a correlated subquery, a function call with side effects): put the cheap, usually-populated argument first.

Two engine exceptions are worth naming in an interview. Unlike COALESCE, which Oracle documents as short-circuiting, Oracle's NVL evaluates both arguments, which is one more reason to prefer COALESCE even there. And SQL Server documents that COALESCE((subquery), x) is rewritten to a CASE that can execute the subquery twice, once for the NULL test and once for the result.

Types are resolved before anything runs. Short-circuiting applies to evaluation, not to type checking. The result type is negotiated across all arguments at parse time, so an unreachable argument can still fail the query:

SELECT COALESCE(1, 'oops');

ERROR:  invalid input syntax for type integer: "oops"

The string never had a chance to be returned, but it still had to be coerced to match the integer, and it can't be. Mixed-type COALESCE calls — int columns defaulted to '', dates defaulted to 'N/A' — fail exactly like this, and the fix is to cast explicitly to the type you actually want out.

COALESCE vs ISNULL, IFNULL, and NVL

Asked at Amazon — Diagnose MySQL joins and GROUP BY/HAVING errors Working in MySQL 8.0 with ONLY_FULL_GROUP_BY enabled, predict the exact output of a series of joins and aggregations over small tables that both contain NULLs, including a row whose join key itself is NULL. Getting the answers right requires knowing precisely how MySQL treats NULL in each clause.

Every major engine ships a proprietary two-argument cousin of COALESCE, and interviewers use them as a dialect check. The differences are real, not cosmetic:

The SQL Server type-inference row is the one that bites. Because ISNULL takes its result type from the first argument, a varchar(2) variable that is NULL forces the replacement value into varchar(2) as well: ISNULL(@v, 'full') returns 'fu', silently truncated, while COALESCE(@v, 'full') returns 'full'. Same inputs, different answers, no warning. If a report built on ISNULL shows chopped strings, this is why.

The practical rule: write COALESCE unless you are in a codebase that has standardized on the vendor function. MySQL, Oracle, and SQL Server all support COALESCE anyway, so portability costs you nothing. The one place the vendor function is arguably better is NVL2(x, a, b) in Oracle (three-way branch on NULL-ness), which has no one-call ANSI equivalent — that's a CASE.

One more thing the Amazon question tests, and it is the bridge to the next two sections: that NULL join key in table B never matches anything, because NULL = NULL is UNKNOWN, not true. Candidates sometimes "fix" it with ON COALESCE(a.id, -1) = COALESCE(b.id, -1). That compiles, and it creates two new problems: a semantic one (do you want NULL keys to match each other?) and a performance one (covered below). Our SQL Not Equal guide walks through the three-valued logic underneath this in detail.

COALESCE(x, 0) in aggregates: sometimes the fix, sometimes the lie

Asked at Capital One — Aggregate exam scores with NULL handling Given students, classes, and a scores table where score is nullable, produce per-group aggregates in one statement, handling the NULLs explicitly; the prompt tells you ISNULL or COALESCE is fair game. The graded part is choosing where the NULL handling goes and being able to defend it.

This is the highest-frequency COALESCE trap in data screens, and Capital One's SQL rounds lean on it hard (their Data Scientist interview guide covers the format). Here is the setup, small enough to trace by hand:

CREATE TABLE scores (
  student_id INT,
  exam_id    INT,
  score      INT   -- NULL = did not sit the exam
);

INSERT INTO scores VALUES
  (1, 10, 90), (1, 11, NULL),
  (2, 10, 70), (2, 11, 80),
  (3, 10, NULL), (3, 11, NULL);

Aggregates already skip NULLs. AVG(score) averages only the scores that exist. Wrapping the column in COALESCE changes the population being averaged:

SELECT student_id,
       COUNT(*)                          AS rows_seen,
       COUNT(score)                      AS exams_scored,
       ROUND(AVG(score), 1)              AS avg_skip_null,
       ROUND(AVG(COALESCE(score, 0)), 1) AS avg_null_as_zero
FROM scores
GROUP BY student_id
ORDER BY student_id;

 student_id | rows_seen | exams_scored | avg_skip_null | avg_null_as_zero
------------+-----------+--------------+---------------+------------------
          1 |         2 |            1 |          90.0 |             45.0
          2 |         2 |            2 |          75.0 |             75.0
          3 |         2 |            0 |          NULL |              0.0

Look at student 1. They scored 90 on the one exam they sat. avg_null_as_zero reports 45.0: the query has decided that missing an exam is the same as scoring zero on it. Student 3 never sat an exam at all and gets an average of 0.0, a failing grade for someone with no data. Whether that is correct is a policy question, not a SQL question. If the school counts absences as zeros, COALESCE(score, 0) inside the AVG is right. If absences are excused, it is a defensible-looking bug.

The interviewer is listening for exactly that sentence. "It depends on whether NULL means absent-counts-as-zero or absent-is-excluded, and here's the query for each" beats either query alone.

There is a second, unambiguous use of COALESCE with aggregates: the empty result. SUM over zero rows (or all-NULL rows) returns NULL, not 0, and downstream arithmetic on that NULL propagates:

SELECT COUNT(*)                  AS all_rows,
       COUNT(score)              AS non_null_scores,
       SUM(score)                AS total,
       COALESCE(SUM(score), 0)  AS total_defaulted
FROM scores
WHERE student_id = 3;

 all_rows | non_null_scores | total | total_defaulted
----------+-----------------+-------+-----------------
        2 |               0 |  NULL |               0

COALESCE(SUM(x), 0) — COALESCE outside the aggregate — changes nothing about the math and only tidies the no-data case. That one is almost always safe. SUM(COALESCE(x, 0)) — inside — rewrites the data. Know which one you are typing.

Asked at TikTok — Compare SQL counts, windows, and NULL semantics Across a users/orders/events schema where one order has a NULL amount, explain and compute the differences between COUNT(*), COUNT(column), and COUNT(DISTINCT column), plus window-function variants. The NULL order amount is placed there specifically to split those counts.

COUNT has the same inside/outside asymmetry, in a sneakier form:

SELECT COUNT(*)                  AS all_rows,
       COUNT(score)              AS scored,
       COUNT(COALESCE(score, 0)) AS coalesced
FROM scores;

 all_rows | scored | coalesced
----------+--------+-----------
        6 |      3 |         6

COUNT(score) counts non-NULL values: 3. Wrap the column in COALESCE and every row now has a value, so the count snaps back to 6 and the distinction you were measuring is gone. A COUNT(COALESCE(col, 0)) in a candidate's query is nearly always a sign they are pattern-matching "NULLs need COALESCE" rather than deciding what the number should mean.

The FULL OUTER JOIN pattern: where COALESCE(x, 0) is exactly right

Asked at Meta — Compute cumulative metrics with full joins You have yesterday's cumulative totals per content item and today's daily values, and either table can be missing items the other has: new content has no history, dormant content has no activity today. Produce today's cumulative total for every item appearing in either table, using a FULL OUTER JOIN.

After a section on how COALESCE(x, 0) lies, here is the pattern where it tells the truth. In an incremental-aggregation pipeline, a missing row has a definite meaning: no history means the running total so far is zero; no activity today means today's contribution is zero. NULL here is "known zero," not "unknown," so defaulting is correct:

CREATE TABLE cumulative_metrics (content_id TEXT, cumulative_value BIGINT);
CREATE TABLE daily_metrics      (content_id TEXT, daily_value BIGINT);

INSERT INTO cumulative_metrics VALUES ('a', 500), ('b', 120);
INSERT INTO daily_metrics      VALUES ('b', 30), ('c', 45);

SELECT COALESCE(y.content_id, d.content_id) AS content_id,
       COALESCE(y.cumulative_value, 0) + COALESCE(d.daily_value, 0) AS new_cumulative
FROM cumulative_metrics y
FULL OUTER JOIN daily_metrics d ON d.content_id = y.content_id
ORDER BY content_id;

 content_id | new_cumulative
------------+----------------
 a          |            500
 b          |            150
 c          |             45

Two distinct COALESCE jobs in one query, and interviewers check both:

1. The key. After a FULL OUTER JOIN, y.content_id is NULL for rows only in d, and vice versa. COALESCE(y.content_id, d.content_id) is the standard way to recover one clean key column. Forget it and content c comes back with a NULL id. (The standard USING (content_id) clause, which SQL Server lacks, does this merge for you; the COALESCE spelling is the fully portable one.)
2. The values. Without the value COALESCEs, content a computes 500 + NULL = NULL and your running total for untouched content evaporates. This is the single most common bug in candidate answers to this question: the join is right, the arithmetic silently destroys it.

The contrast with the exam-scores section is the actual lesson: same function, same second argument, opposite verdicts, because the meaning of the missing row differs.

COALESCE in WHERE and ON: correct answers, killed indexes

Asked at Robinhood — Identify Transactions During 'Golden' Membership Period Given a transactions table and a membership table where an active membership has a NULL end_date, return the transactions that occurred while the user held a golden membership. The open-ended NULL is the crux: every correct answer has to treat "no end date" as "still active."

The natural answer sentinels the NULL end date into the far future:

CREATE TABLE membership (
  user_id    INT,
  tier       TEXT,
  start_date DATE,
  end_date   DATE   -- NULL = still active
);

CREATE TABLE transactions (
  trans_id   INT,
  user_id    INT,
  trans_date DATE,
  amount     NUMERIC(10,2)
);

INSERT INTO membership VALUES
  (101, 'golden', DATE '2022-12-15', NULL),
  (102, 'silver', DATE '2023-01-01', DATE '2023-02-01'),
  (103, 'golden', DATE '2023-02-01', DATE '2023-03-01');

INSERT INTO transactions VALUES
  (1, 101, DATE '2023-01-05', 200.00),
  (2, 102, DATE '2023-01-07', 150.50),
  (3, 101, DATE '2023-02-03',  75.00),
  (4, 103, DATE '2023-02-10',  50.00);

SELECT t.trans_id, t.user_id, t.trans_date, t.amount
FROM transactions t
JOIN membership m
  ON m.user_id = t.user_id
 AND m.tier = 'golden'
 AND t.trans_date >= m.start_date
 AND t.trans_date <= COALESCE(m.end_date, DATE '9999-12-31')
ORDER BY t.trans_id;

 trans_id | user_id | trans_date | amount
----------+---------+------------+--------
        1 |     101 | 2023-01-05 | 200.00
        3 |     101 | 2023-02-03 |  75.00
        4 |     103 | 2023-02-10 |  50.00

Correct, and in this shape mostly harmless: the join still seeks on user_id, and the COALESCE only post-filters the matched rows. The trouble starts when the COALESCE-wrapped column is the one the database needs to search by. Consider "which memberships were active on 2023-02-15":

SELECT user_id, tier
FROM membership
WHERE COALESCE(end_date, DATE '9999-12-31') > DATE '2023-02-15'
  AND start_date <= DATE '2023-02-15'
ORDER BY user_id;

 user_id |  tier
---------+--------
     101 | golden
     103 | golden

Right answer, wrong plan at scale. COALESCE(end_date, ...) is an expression, not the column, so an ordinary index on end_date cannot be range-scanned: the predicate is non-sargable and the engine falls back to reading every row. On three rows nobody cares. At production row counts, the full scan is the whole cost of the query.

The standard rewrite splits the NULL branch out where each half can use an index:

SELECT user_id, tier
FROM membership
WHERE end_date > DATE '2023-02-15'
  AND start_date <= DATE '2023-02-15'
UNION ALL
SELECT user_id, tier
FROM membership
WHERE end_date IS NULL
  AND start_date <= DATE '2023-02-15'
ORDER BY user_id;

 user_id |  tier
---------+--------
     101 | golden
     103 | golden

Same rows. The first branch is a plain range predicate on end_date; the second is an IS NULL test, which B-tree indexes handle directly in PostgreSQL (and which a small partial index — WHERE end_date IS NULL — makes nearly free, since active rows are usually the minority). UNION ALL rather than UNION because the branches cannot overlap and you don't want to pay for deduplication. A simple (end_date IS NULL OR end_date > ...) is fine too when the optimizer handles the OR well, but the UNION ALL form is the one that behaves predictably across engines.

The same reasoning condemns ON COALESCE(a.k, -1) = COALESCE(b.k, -1) from the Amazon question earlier: it turns an indexable equality join into an expression join, and it invents a rule (NULL keys match each other) that the data model may not intend. If you genuinely want NULL-safe equality, say so in the engine's own words — IS NOT DISTINCT FROM in PostgreSQL, <=> in MySQL — and expect the interviewer to ask why the keys are NULL in the first place. That is usually the better conversation.

Division guards: NULLIF first, then decide — don't reflex-COALESCE

Asked at Meta — Compute CTR overall and by campaign type From an ad-events log, compute click-through rate overall and per campaign type for a fixed one-week window, after deduplicating repeated log rows. The prompt requires you to prevent division by zero — some campaign types can have clicks logged with no impressions at all.

CTR is clicks / impressions, and the moment a group has zero impressions the naive division dies. The idiomatic guard is NULLIF, COALESCE's mirror image: NULLIF(a, b) returns NULL when a = b, so NULLIF(impressions, 0) converts the crash case into a NULL that division then propagates:

CREATE TABLE ad_events (
  ad_id         INT,
  campaign_type TEXT,
  event         TEXT   -- 'impression' or 'click'
);

INSERT INTO ad_events VALUES
  (1, 'search',  'impression'),
  (1, 'search',  'impression'),
  (1, 'search',  'click'),
  (2, 'display', 'impression'),
  (3, 'video',   'click');

SELECT campaign_type,
       COUNT(*) FILTER (WHERE event = 'click')      AS clicks,
       COUNT(*) FILTER (WHERE event = 'impression') AS impressions,
       ROUND(
         COUNT(*) FILTER (WHERE event = 'click')::numeric
         / NULLIF(COUNT(*) FILTER (WHERE event = 'impression'), 0),
         2
       ) AS ctr
FROM ad_events
GROUP BY campaign_type
ORDER BY campaign_type;

 campaign_type | clicks | impressions | ctr
---------------+--------+-------------+------
 display       |      0 |           1 | 0.00
 search        |      1 |           2 | 0.50
 video         |      1 |           0 | NULL

(FILTER and the ::numeric cast are PostgreSQL spellings; portable SQL uses SUM(CASE WHEN event = 'click' THEN 1 ELSE 0 END) and CAST(... AS numeric). The cast matters on its own — integer divided by integer is integer division in PostgreSQL and SQL Server, and every CTR would come back 0.)

Now the trap. The reflex is to finish the job with COALESCE(..., 0) so the report has no NULLs. Look at the video row before you do: one click, zero impressions. A CTR of 0 means "shown, never clicked" — that is display, and 0.00 is the honest value there. video is something else entirely: clicks arriving without impressions means broken or missing logging, and its CTR is undefined. COALESCE-ing it to 0 relabels "our tracking is broken" as "this campaign performs terribly," and that number will get a campaign killed by someone three dashboards downstream who never saw the raw counts.

Leaving the NULL is a feature: it is the query telling you this group needs investigation, not a default. If the report format cannot tolerate NULL, surface the distinction some other way (a flag column, or excluding undefined groups with a comment). Similar edge-case discipline is what the Google violation-rate question grades: it asks you to be explicit about every denominator before writing any SQL.

Practice these on PracHub

Each of these is a real, company-tagged question where NULL handling decides the outcome. Work them in the full question bank, and if you want the broader syntax refresher first, start with the Top 50 SQL interview questions.

Drill the core fallback pattern:

• Select max-discount product per category (TikTok) — COALESCE inside an expression, inside a window function's ORDER BY, with a tie-break.
• Identify Transactions During 'Golden' Membership Period (Robinhood) — the open-ended NULL date range, and whether your answer stays index-friendly.

Drill aggregates over NULLs:

• Aggregate exam scores with NULL handling (Capital One) — where the COALESCE goes: inside the aggregate or outside it.
• Compare SQL counts, windows, and NULL semantics (TikTok) — COUNT(*) vs COUNT(col) vs COUNT(DISTINCT col) with a planted NULL.

Drill the pipeline patterns:

• Compute cumulative metrics with full joins (Meta) — FULL OUTER JOIN plus COALESCE on both keys and values.
• Compute CTR overall and by campaign type (Meta) — NULLIF division guards and the choice to keep the NULL.