Why does putting a filter predicate in a MERGE ON clause cause unwanted INSERTs?
answer
- ON is a match test, not a filter
- hidden target rows look brand new
- the insert arm fires on the same key
- move state conditions onto the arm
- keep key equality alone in ON
basics
~20 sON only classifies source rows as matched or not matched. An existing target row excluded by an extra predicate in ON is reported as not matched, so the insert arm adds a second row for the same key — a duplicate or a unique-constraint failure.
solid answer
~40 sThe ON clause is a *matching* condition, not a filter on which target rows the statement is allowed to touch. Write `ON (t.id = s.id AND t.status = 'ACTIVE')` and an inactive row with that id stops satisfying ON; the source row then finds no match, is classified not matched, and falls into `WHEN NOT MATCHED THEN INSERT`. You get a duplicate key rather than a skipped row — or a constraint violation if the target enforces uniqueness. Conditions that decide *whether to act* belong on the arm: `WHEN MATCHED AND t.status = 'ACTIVE' THEN UPDATE SET ...`. Conditions that decide *which incoming rows to process* belong in the `USING` query's WHERE. Keep ON to the identity of the row — the key columns and nothing else.
code
sql · 10 lines-- WRONG: an existing inactive customer fails ON, so it looks "not matched"
-- and the insert arm creates a duplicate customer_id
MERGE INTO customer_dim t
USING staging_customer s
ON (t.customer_id = s.customer_id AND t.status = 'ACTIVE')
WHEN MATCHED THEN
UPDATE SET city = s.city
WHEN NOT MATCHED THEN
INSERT (customer_id, city, status)
VALUES (s.customer_id, s.city, 'ACTIVE');go deeper
Remember the rule of thumb: the ON clause holds the key equality and nothing else. Extra conditions there make existing rows look new, and the merge inserts duplicates.
Explain the classification step that causes it — a target row excluded by ON simply is not found, so the source row is not matched — and know that arm conditions and the source query's WHERE are the correct homes for filters.
Catch this in review by reading every ON clause for non-key predicates, and be able to say what the safe alternatives are when someone genuinely needs to restrict the target: guarded arms, a view, or splitting into separate statements.
Set the convention and the guardrails: uniqueness enforced on merge keys so this bug fails loudly instead of duplicating data, plus load-pipeline checks on row counts that would catch an unexpected insert wave.
## Three places a condition can live, and they mean different things A MERGE has three slots that all look like "a WHERE clause" and behave completely differently: 1. **Inside `USING (...)`** — restricts which *source* rows take part at all. Rows filtered out here simply do not exist for the merge. 2. **In `ON (...)`** — decides, for each surviving source row, whether the target already has a corresponding row. Output: matched or not matched. 3. **On an arm, as `WHEN MATCHED AND ...`** — decides whether that arm's action runs for a row already classified. Misplacing a condition between slots 2 and 3 is the classic MERGE bug, and it is silent until the duplicates show up. ## The failure ```sql -- WRONG: the status test lives in ON MERGE INTO customer_dim t USING staging_customer s ON (t.customer_id = s.customer_id AND t.status = 'ACTIVE') WHEN MATCHED THEN UPDATE SET city = s.city WHEN NOT MATCHED THEN INSERT (customer_id, city, status) VALUES (s.customer_id, s.city, 'ACTIVE'); ``` The author's intent was "only refresh active customers". What the statement says is "a customer counts as existing only if it is active". For customer 42, present in the target but with status `'INACTIVE'`, no target row satisfies ON. The source row is therefore **not matched**, the insert arm fires, and the table gains a *second* row for customer 42. If `customer_id` is the primary key, the statement instead dies with a unique-constraint violation — which is the lucky outcome, because at least it is visible. ## The fix Move the condition to the arm, leaving ON to express identity only: ```sql MERGE INTO customer_dim t USING staging_customer s ON (t.customer_id = s.customer_id) WHEN MATCHED AND t.status = 'ACTIVE' THEN UPDATE SET city = s.city WHEN NOT MATCHED THEN INSERT (customer_id, city, status) VALUES (s.customer_id, s.city, 'ACTIVE'); ``` Now customer 42 is matched — the target does have that customer — but the matched arm's condition is false, so no action is taken. The row is left alone, which is what "only refresh active customers" meant. Nothing is inserted, because a matched row never reaches the not-matched arm even if every matched arm declines to act. ## The same rule stated as a heuristic > **ON answers "is this row already here?" Nothing else belongs in it.** Practically: the ON condition should contain the key equality and, at most, other columns that genuinely form part of the row's identity in a composite key. Anything about the row's *state* — status flags, effective dates, soft-delete markers, version numbers — is a decision about whether to act, and therefore an arm condition. Anything about the *incoming batch* — only today's records, only rows from one region — is a source filter and belongs in the USING query. ## Two related traps **Trying to narrow the target.** Standard MERGE has no WHERE clause of its own, so there is no supported way to say "only consider this slice of the target". Attempting to express that through ON produces exactly the duplicate-insert bug above. If you truly need to restrict which target rows participate, the honest options are to make the restriction part of the row's identity (rarely true), to guard every arm with the condition and accept that unmatched rows will insert, or — in engines that support it — to merge into a view or a derived subset of the target. Do not smuggle it into ON. **Skipping no-op updates.** "Only update if something changed" is also an arm condition, not an ON condition: `WHEN MATCHED AND (t.city <> s.city OR t.name <> s.name) THEN UPDATE ...`. Put that test in ON and every unchanged row becomes an unwanted insert. Note also that `<>` between values that may be NULL yields unknown rather than true, so a null-safe comparison is needed when the columns are nullable. ## How it shows up in review The tell is an ON clause containing anything other than equality on key columns. When you see one, ask what the author meant: if it is about identity, fine; if it is a business rule, it is in the wrong slot and the statement is one production batch away from duplicating rows.
- If a matched row's arm condition is false, can the not-matched arm still fire for it?No. Classification happens once: a source row that found a target row is matched, full stop. If every matched arm's condition evaluates false, nothing happens for that row. The not-matched arm is only reachable for rows the ON condition matched nowhere.
- How do you restrict a MERGE to only part of the target table?Standard MERGE gives you no clean way — it has no WHERE clause, and encoding the restriction in ON causes duplicate inserts. Guard each arm with the condition and accept that unmatched source rows still insert, merge into a view or subset where the engine allows it, or split the work into explicit UPDATE and INSERT statements.
- Where does an 'only update when a column actually changed' test belong?On the matched arm: `WHEN MATCHED AND (t.city <> s.city OR t.name <> s.name) THEN UPDATE ...`. In ON it would reclassify unchanged rows as new and insert them. With nullable columns, use a null-safe comparison, since `<>` involving NULL yields unknown rather than true.
saying these in an interview costs you the question
- Treats ON as a WHERE clause for the target table
- Expects rows excluded by ON to be silently skipped
- Adds business-rule predicates to ON to limit the update
- Fixes the duplicates by dropping the not-matched arm
- Believes a failed arm condition falls through to the insert arm