NULL-Set Trap

Null represents unknown, indeterminate value, so any value (including null values) and null value comparisons are unknowable, in the When clause of the ON clause, where clause, merge or case, the result of any value and null comparison is false, This is the trap of the null set, and I have been in the pit.

Once, I used merge to synchronize the data because null values existed in the target table, although null values were handled in the source table, but the null values in the target table were ignored, causing the data merge to fail.

Step1, creating sample Data

--Create source tableCreate TableDbo.dt_source (IDint NULL, Codeint NULL) on [Primary] with(data_compression=page)--Create target tableCreate TableDbo.dt_target (IDint NULL, Codeint 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, both in the source table and in the target table, to avoid comparisons with null values.

--insert data into tableInsert  intoDbo.dt_source (Id,code)Values(1,1),(2,2),(3,NULL)Insert  intodbo.dt_target (Id,code)Values(1,1),(2,NULL)

STEP3, error notation: Only NULL in the source table is processed, and NULL in the target table is ignored

---1 stand for Unknwon valueMerge Dbo.dt_target tusing dbo.dt_source s onT.id=s.id whenMatched and(T.code<>IsNull(S.code,-1))     Then Update        SetT.code=S.code when  notmatched Then Insert(Id,code)Values(S.id,s.code);

View the data in the target and Srouce tables, the data is unsynchronized, and the reason for the unsynchronized is when the and condition after the matched clause A null value exists in T.code, and the result of a null value and any value (including a null value) is unknown, which is treated as false in the When clause.

Correct notation 1, whether in the target table, or in the source table, as long as there is a null value, must be processed to avoid the occurrence and null comparisons.

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

---1 stand for Unknwon valueMerge Dbo.dt_target tusing dbo.dt_source s onT.id=s.id whenMatched and(IsNull(T.code,-1)<>IsNull(S.code,-1))     Then Update        SetT.code=S.code when  notmatched Then Insert(Id,code)Values(S.id,s.code);

Correct notation 2, in the conditional clause, use is NULL or is not NULL to process the null value.

Tsql uses is null and is not NULL to be true, not null. NULL is NULL for the logical value True,other_value is null to FALSE, Other_value is not NULL to TRUE.

Merge Dbo.dt_target tusing dbo.dt_source s onT.id=s.id whenMatched and(T.code<>S.codeorT.code is NULL orS.code is NULL)     Then Update        SetT.code=S.code when  notmatched Then Insert(Id,code)Values(S.id,s.code);

NULL-Set Trap