The trap of NULL: Merge

NULL stands for unknown, uncertain value, so any value (including null value) and null value comparison is unknowable. In the on clause, where clause, Merge or case when clause, the result of any value and null comparison is false, this is the trap set by NULL, I have been tricked.

On one occasion, I used Merge to synchronize data. Because of the null values in the target table, although the null values were processed in the source table, the null values in the target table were ignored, causing the data merge to fail.

Step1, creating sample data

Create source tablecreate table dbo.dt_source (id int null,code int null) on [primary] with (data_compression=page)-- create target tablecreate table dbo.dt_target (id int null,code int null) on [primary] with (data_compression=page)

Step2, inserting sample data

In the sample data, null values exist in both the Source table and the Target table, and comparisons with null values should be avoided, whether in the Source table or in the Target table.

-- insert data into tableinsert into dbo.dt_source (id,code) values (1), (2), (3) insert into dbo.dt_target (id,code) values (1), (2)

Step3, misspelled: only the null in the Source table is processed and the null in the Target table is ignored

-1 stand for unknwon valuemerge dbo.dt_target tusing dbo.dt_source s on t.id=s.idwhen matched and (t.codeisnull (s. Codemai 1)) then update set t.code=s.codewhen not matched then insert (id,code) values (s. Idret s. Code)

Looking at the data in the Target and Srouce tables, the data is out of sync because of the and condition after the when matched clause. There is a null value in the t.code. The result of the comparison between the null value and any value (including the null value) is unknown, which is regarded as false in the when clause.

Correct writing method 1, whether in the target table or in the source table, as long as there is a null value, it must be processed to avoid comparison with null.

The way to handle this is to use a value to represent unknwon, and if the valid value of the ID column cannot be negative, you can use-1 instead of unknown. Because-1 and-1 are equal, null values and null values are logically treated as the same.

-1 stand for unknwon valuemerge dbo.dt_target tusing dbo.dt_source s on t.id=s.idwhen matched and (isnull (t. Codejay 1) isnull (s.codemae Metho1)) then update set t.code=s.codewhen not matched then insert (id,code) values (s.idjold s.code)

Correctly write method 2, in the conditional clause, use is null or is not null to deal with null values.

Tsql uses is null and is not null to really be, not null. The logical value of null is null is that true,other_value is null is false and other_value is not null is true.

Merge dbo.dt_target tusing dbo.dt_source s on t.id=s.idwhen matched and (t.codes.code or t.code is null or s.code is null) then update set t.code=s.codewhen not matched then insert (id,code) values (s.idres.code)