The MERGE That Would Not Update: A NULL in Your Change Detection
You wrote an idempotent MERGE to categorize a product. You run it, SSMS says 0 rows affected with no error, and the product still has no category. You run it again and nothing changes. The row is right there, the ON clause matches it, and the UPDATE will not fire.
This is Part 1 of a series on NULL traps in T-SQL, “The UNKNOWN Problem, aka Three-Valued Logic”. Every trap in it comes from one place: a comparison against NULL is neither true nor false, and the parts of SQL that decide whether to keep a row act only on true. This first one is in the change-detection predicate that a lot of MERGE upserts use.
Here is the setup. A products table where a product can start life without a category, so category_id is nullable.
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE dbo.product ( product_id int NOT NULL CONSTRAINT pk_product PRIMARY KEY , sku varchar(20) NOT NULL , product_name varchar(100) NOT NULL , category_id int NULL , list_price decimal(10,2) NULL ); INSERT INTO dbo.product (product_id, sku, product_name, category_id, list_price) VALUES (100, 'BOLT-08', 'M8 Hex Bolt', NULL, 0.12); |
Now a MERGE that upserts one product and only updates when something changed.[1] This is a common shape: compare each column, and if any differs, write the new values.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
MERGE INTO dbo.product AS d USING ( VALUES (100, 'BOLT-08', 'M8 Hex Bolt', 4, 0.12) ) AS s (product_id, sku, product_name, category_id, list_price) ON d.product_id = s.product_id WHEN MATCHED AND ( d.product_name <> s.product_name OR d.category_id <> s.category_id OR d.list_price <> s.list_price ) THEN UPDATE SET d.product_name = s.product_name , d.category_id = s.category_id , d.list_price = s.list_price WHEN NOT MATCHED BY TARGET THEN INSERT (product_id, sku, product_name, category_id, list_price) VALUES (s.product_id, s.sku, s.product_name, s.category_id, s.list_price); |
The product should come out of that with category_id = 4. It does not. Zero rows update, and the product stays uncategorized.
|
1 2 3 |
SELECT product_id, category_id FROM dbo.product WHERE product_id = 100; /* product_id category_id 100 NULL */ |
The cause is three-valued logic. SQL Server evaluates a predicate as one of TRUE, FALSE, or UNKNOWN, and an ordinary comparison against NULL (with =, <>, <, or >) comes back UNKNOWN. Here d.category_id is NULL, so d.category_id <> 4 is UNKNOWN rather than TRUE. The other two columns match, so those comparisons are FALSE. The whole AND predicate reduces to FALSE OR UNKNOWN OR FALSE, which is UNKNOWN. WHEN MATCHED AND keeps the match only when its predicate is TRUE, so it drops this row and updates nothing. No error is raised, because nothing went wrong as far as the engine is concerned. (SET ANSI_NULLS OFF does not help here. It only changes comparisons against the NULL literal, such as x = NULL, not a column-to-column comparison like this one, and it is deprecated regardless.)
The change that got dropped is a column going from NULL to a real value, like giving a product its first category. That is the case the predicate most needs to catch, and it is the one it misses.
There are two ways to fix it. The first is to spell out the NULL cases for every nullable column.
|
1 2 3 4 5 6 7 8 9 10 |
WHEN MATCHED AND ( d.product_name <> s.product_name OR d.category_id <> s.category_id OR (d.category_id IS NULL AND s.category_id IS NOT NULL) OR (d.category_id IS NOT NULL AND s.category_id IS NULL) OR d.list_price <> s.list_price OR (d.list_price IS NULL AND s.list_price IS NOT NULL) OR (d.list_price IS NOT NULL AND s.list_price IS NULL) ) THEN |
That is correct. Both NULL means no change, one side NULL means a change, and two present values fall back to the plain comparison. The cost is that every nullable column now needs three conditions instead of one, and every new nullable column has to repeat both IS NULL checks. On a wide table that predicate gets long and hard to scan.
The second fix hands the NULL handling to INTERSECT.[2]
|
1 2 3 4 5 6 |
WHEN MATCHED AND NOT EXISTS ( SELECT d.product_name, d.category_id, d.list_price INTERSECT SELECT s.product_name, s.category_id, s.list_price ) THEN |
INTERSECT compares the two rows as rows and treats NULL as equal to NULL. So SELECT d... INTERSECT SELECT s... returns a row only when the destination and source match on every listed column, NULLs included. Wrap it in NOT EXISTS and the predicate is true whenever the two rows differ. To cover another column you add it to both SELECT lists. You do not pick a sentinel value, and there are no per-column NULL legs to keep in sync.
EXCEPT gives you the same answer written the other way (WHEN MATCHED AND EXISTS (SELECT d... EXCEPT SELECT s...)), so use whichever reads better to you. The comparison is NULL-safe because INTERSECT and EXCEPT use the same equality that GROUP BY and DISTINCT use[3], where NULLs group together. varchar(max) and nvarchar(max) columns are allowed in these set operators, so a wide nvarchar(max) column compares fine. The types that cannot take part are text, ntext, image, and xml, which are not comparable; cast or handle those separately.
Run the MERGE again with either fix and the product lands in category 4, which is what the upsert was supposed to do.
This trap is not unique to MERGE. Any conditional update of the form UPDATE ... WHERE existing_value <> new_value skips the NULL-to-value change the same way, so both fixes carry over. The change detection is also doing something deliberate. A row that compares equal runs no UPDATE, so it fires no update trigger, bumps no rowversion, and shows no UPDATE action in OUTPUT. That is usually what you want, and it is why the missed change gives no signal. Choose the fix on correctness and readability rather than speed, because INTERSECT adds a small distinct step and on a large source you should compare the plans for your row counts. And if this MERGE is a concurrent upsert rather than a single-writer patch, it also needs a unique target key and the right locking such as HOLDLOCK, which is a separate subject from the NULL predicate.
The bare <> predicate is easy to trust because it looks complete and works in every test where the starting values are not NULL. It only fails on the NULL-to-value change, which is easy to miss until a config row or a product mapping refuses to update in production and nothing flags it.
Next up is Part 2, NOT IN with a subquery, where a single NULL in the list throws away every row instead of one.
If you have hit this MERGE behavior, or you have a house style for NULL-safe change detection, I would like to hear it. You can find me on Bluesky and LinkedIn.
References
- MERGE (Transact-SQL) - Microsoft Learn. Defines the MERGE statement and the WHEN MATCHED AND change-detection clause. ↩
- Undocumented Query Plans: Equality Comparisons - sql.kiwi, Paul White. Works the same change-detection problem, shows the NULL-safe rewrite, and explains how INTERSECT compares rows including NULLs. ↩
- EXCEPT and INTERSECT (Transact-SQL) - Microsoft Learn. Documents how these set operators compare rows and treat two NULLs as equal. ↩