What can go wrong when you join on COALESCE(a.code, 'N/A') = COALESCE(b.code, 'N/A')?
answer
- it turns unknown into an ordinary value
- all the missing-key rows now share one key
- think about how many rows that produces
- what if the placeholder is real data one day
- both sides are now function expressions
basics
~20 sIt makes every NULL-keyed row on one side match every NULL-keyed row on the other, multiplying rows; and if the sentinel ever appears in real data, genuine rows join to missing ones. Wrapping both columns in a function also blocks ordinary index use.
solid answer
~50 sThe rewrite does what it was asked to do — it turns NULL into a comparable value — but it brings three problems. First, NULL stops being an unknown and becomes a single shared key, so the NULL group on the left joins to the entire NULL group on the right: hundreds of rows a side become tens of thousands of output rows. Second, the sentinel is safe only while it can never occur naturally; the day a real `'N/A'` code is loaded, those rows silently join to rows whose code is missing, and the result is wrong rather than merely large. Third, both sides of the comparison are now function expressions rather than plain columns, so an ordinary index on `code` generally cannot be used the way it could for `a.code = b.code`. If you genuinely want NULL to match NULL, prefer `ON a.code IS NOT DISTINCT FROM b.code`; if you do not, keep equality and handle the NULL-keyed rows explicitly.
code
sql · 8 lines-- a.code = (NULL, 'X'); b.code = (NULL, NULL)
SELECT COUNT(*)
FROM a JOIN b ON COALESCE(a.code,'~') = COALESCE(b.code,'~');
-- 2: the one NULL-coded row in a pairs with BOTH NULL-coded rows in b
SELECT COUNT(*)
FROM a JOIN b ON a.code = b.code;
-- 0: under equality no NULL key matches anythinggo deeper
Understand what the rewrite does: it replaces NULL with a real value so equality can match. Knowing that it therefore makes all the missing-key rows match each other is the key takeaway.
Enumerate the failure modes — cross-multiplied NULL groups, collision with a genuine sentinel value, function-wrapped columns — and give the null-safe predicate as the more honest way to express matching NULLs.
Lead with the semantic objection: the query asserts that two unknowns are the same unknown. Then show the row-count arithmetic for the NULL groups and describe how you would audit an existing query that already does this.
Push the fix into the model. Decide whether the column should be NOT NULL with a declared unknown member, so the placeholder is enforced data rather than a string literal every query author has to reinvent and keep consistent.
## What the rewrite is trying to fix Equality never matches NULL, so rows with a missing key drop out of an inner join. A common reflex is to substitute a placeholder on both sides: ```sql SELECT a.id, b.id FROM a JOIN b ON COALESCE(a.code, 'N/A') = COALESCE(b.code, 'N/A'); ``` Now the comparison is between two non-NULL strings and the missing-key rows participate. That is the whole appeal — no special predicate, works in any dialect. It is also a trap in three distinct ways, and a strong answer names all three. ## Problem 1: NULL becomes one shared value, so the NULL groups cross-multiply Under `=`, unknown never equals unknown, so a row with a missing code contributes nothing. Under the sentinel, every missing code on the left is the string `'N/A'`, and so is every missing code on the right — they all match each other. If 500 left rows and 300 right rows have a NULL code, that group alone emits 150,000 rows where the original query emitted zero. ```sql -- a.code = (NULL, 'X'), b.code = (NULL, NULL) SELECT COUNT(*) FROM a JOIN b ON COALESCE(a.code,'~') = COALESCE(b.code,'~'); -- 2 -- the single NULL-coded a row pairs with both NULL-coded b rows ``` These pairings are almost never meaningful: two records that are both missing a code have not been shown to be about the same thing. So the query grows, slows, and produces rows nobody wanted. This is the failure people actually hit in production, usually when a data feed starts leaving a field empty. ## Problem 2: the sentinel can collide with real data The technique is correct only while `'N/A'` is impossible as a genuine code. That assumption belongs to a moment in time, not to the schema — nothing in the database enforces it. When a source system starts emitting a literal `'N/A'`, or someone types it into an import file, those rows begin joining to rows whose code is *missing*, which is an entirely different fact. The output is not obviously broken: it is a plausible number that is quietly wrong, and the join looks innocent in review. Picking an exotic sentinel such as `'~~none~~'` lowers the odds without removing the class of defect, and it still depends on the column being wide enough and on the types matching, since `COALESCE` requires compatible types across its arguments. ## Problem 3: the comparison is no longer over plain columns `COALESCE(a.code, 'N/A') = COALESCE(b.code, 'N/A')` compares two computed expressions. An index defined on `code` describes the column's values, not the function's, so the planner generally cannot use it to satisfy this predicate, and the join method it picks may change. Engines differ in how far they can see through such expressions, so verify on yours rather than assuming — but treat "I wrapped both join columns in a function" as a signal worth checking. ## What to do instead **If NULL genuinely should match NULL** — you are reconciling two extracts of the same optional attribute — use the null-safe predicate rather than a sentinel: ```sql JOIN b ON a.code IS NOT DISTINCT FROM b.code ``` It states the intent directly, cannot collide with any data value, and is impossible to misread. Note that it does *not* fix problem 1: the NULL groups still match each other many-to-many, because that is exactly what you asked for. Check the size of the NULL group on both sides before enabling it either way. **If NULL should not match NULL** — the usual truth — keep `=` and deal with the missing-key rows deliberately. Use an outer join to keep them visible, or route them to an exception report: ```sql SELECT a.id, b.id FROM a LEFT JOIN b ON a.code = b.code; SELECT * FROM a WHERE a.code IS NULL; -- the rows to investigate ``` **If the column keeps causing this**, fix the model. Make `code` `NOT NULL` and add an explicit "unknown" member row in the referenced table. That is the sentinel idea done properly: the placeholder becomes declared data with a key and a constraint behind it, instead of a string literal repeated in every query that touches the table. ## How to argue it in an interview The strongest answer separates the semantic objection from the performance one. The semantic objection is decisive on its own: `COALESCE` asserts that two missing values are the same missing value, which is a claim about your data that is usually false. The performance objection — cross-multiplied NULL groups and an index that no longer applies — is what makes the mistake expensive as well as wrong.
- Does IS NOT DISTINCT FROM avoid all three problems?It removes two of them. There is no literal to collide with real data, and the intent is explicit rather than encoded in a magic string. But NULL still matches NULL, so the many-to-many pairing of the NULL groups is unchanged — that is the semantics you asked for — and index usability still needs checking on your engine.
- When is a sentinel-based join actually reasonable?When the placeholder is declared rather than invented — a real "unknown" member row in the referenced table, with the foreign key made NOT NULL so nothing can be missing. At that point ordinary equality does the work, no query needs a special predicate, and the constraint prevents the collision case entirely.
- How would you spot this pattern doing damage in an existing query?Compare its row count against the same join written with plain equality, and count NULL keys on both sides: the product of those two counts is the block of pairings the sentinel invented. Then check whether the sentinel string can occur as data with a simple `WHERE code = 'N/A'` against both tables.
saying these in an interview costs you the question
- Says COALESCE on both sides makes the join correct
- Ignores that all NULL rows then match each other
- Assumes the sentinel value can never appear in the data
- Applies COALESCE to only one side of the predicate
- Treats the rewrite as a performance optimisation