How does a MERGE evaluate multiple WHEN MATCHED arms carrying AND conditions?

## Arms are a chain of conditions, not a set of independent rules A MERGE arm has the shape `WHEN MATCHED [AND <condition>] THEN <action>` or `WHEN NOT MATCHED [AND <condition>] THEN INSERT ...`. You may write more than one arm of a kind. For each row the engine considers the arms of the applicable kind **in the order they appear** and applies the **first** one whose condition evaluates true. Everything after that is skipped. Two consequences follow immediately: - A row receives **at most one** action per MERGE. Arms are not filters that each get a turn. - **Order is meaning.** Swapping two arms can change the result, exactly as reordering the branches of a `CASE` expression can. ## A delta load reads top-down ```sql MERGE INTO product t USING product_delta s ON (t.sku = s.sku) WHEN MATCHED AND s.op = 'D' THEN DELETE WHEN MATCHED AND (t.price <> s.price OR t.name <> s.name) THEN UPDATE SET price = s.price, name = s.name WHEN NOT MATCHED AND s.op <> 'D' THEN INSERT (sku, name, price) VALUES (s.sku, s.name, s.price); ``` Read it as a policy: *if the feed says this SKU is gone, delete it; otherwise if anything actually changed, update it; otherwise leave it alone; and if the SKU is new and not a deletion, insert it.* Note what the third case gets you for free — a matched row whose values are identical satisfies no matched arm, so nothing is written for it. That is the idiomatic way to suppress no-op updates in a MERGE, and it matters because a no-op UPDATE is still a write as far as the engine, triggers and any change-data capture are concerned. Swap the first two arms and the delete never happens for a row whose values also differ — the update arm would claim it first. The ordering encodes the priority of the rules. ## Unconditional arms terminate the chain `WHEN MATCHED THEN UPDATE ...` with no `AND` is true for every matched row. Any later `WHEN MATCHED` arm is therefore dead code; engines typically reject or warn about the unreachable arm rather than silently ignoring it. The habit that avoids the problem is simple: within each kind, write the conditional arms first and the unconditional catch-all — if you want one at all — last. ## The NULL caveat in arm conditions A change-detection condition like `t.price <> s.price` returns *unknown*, not true, when either side is NULL, and an arm only fires on true. So a row whose price went from NULL to 9.99 would not be seen as changed. When the columns are nullable you need a null-safe comparison — standard SQL provides `IS DISTINCT FROM` for exactly this: `WHEN MATCHED AND (t.price IS DISTINCT FROM s.price OR t.name IS DISTINCT FROM s.name) THEN ...`. Engine support for that predicate varies, so verify it before relying on it. ## Portability of multi-arm MERGE The standard permits repeated arms with conditions, but implementations impose their own limits — SQL Server, for instance, allows at most two `WHEN MATCHED` clauses and requires the first of them to carry a condition. If your policy has three or four matched cases, either fold them into fewer arms with richer conditions and `CASE` expressions in the SET list, or split the work into separate statements. Check the specific engine's documentation for the cap rather than assuming the standard's permissiveness. ## When one MERGE is too clever A statement with five arms and nested conditions is compact but hard to test and hard to explain in review, and a mistake in arm order is invisible until the wrong rows disappear. If the reconciliation rules are genuinely complex — several kinds of tombstone, effective-dated history, conditional promotion between statuses — expressing them as a few clearly named statements against a staging table is often the more maintainable choice, and it makes each rule independently testable. The multi-arm MERGE earns its keep when the policy is small enough to read in one screen.