SQL 联接匹配空列值

Question

我正在使用MS SQL Server 2012中的JOIN删除一个表中与另一个表中的记录匹配的记录

DELETE t1
FROM Table1 t1
JOIN Table2 t2
ON 
t1.[Column1] = t2.[Column1] 
AND t1.[Column2] = t2.[Column2] 
AND t1.[Column3] = t2.[Column3] 

但是，如果两个表中的列都包含null则不会匹配和删除它们。如果两列都包含null，我如何修改查询以匹配记录，而无需添加特定的null检查

DELETE t1
FROM Table1 t1
JOIN Table2 t2
ON 
t1.[Column1] = t2.[Column1] OR (t1.[Column1] is null and t2.[Column1] is null)
AND t1.[Column2] = t2.[Column2] OR (t1.[Column2] is null and t2.[Column2] is null)
AND t1.[Column3] = t2.[Column3] OR (t1.[Column3] is null and t2.[Column3] is null)

Answer 1

这是一个真正的问题，因为 SQL Server 没有NULL-safe 比较运算符。 ANSI 标准运算符是IS NOT DISTINCT FROM。

OR的问题在于它排除了索引的使用。ISNULL()的问题是一样的。 因此，如果您的表有任何大小，您希望避免使用它们。

您可以做的一件事是将值设置为默认值。 我不知道什么默认值会避免现有值，但这可能看起来像：

update table1
set column1 = coalesce(column1, ''),  -- for strings
column2 = coalesce(column2, -1),  -- for numbers
column3 = coalesce(column3, cast('1900-01-01' as date))  -- for dates
where column1 is null or column2 is null or column3 is null;

您需要在两个表上执行此操作。 然后，您可以在删除后恢复NULL值。

实际上，在 SQL Server 中，您可以添加计算列：

alter table1 add column1_notnull as (coalesce(column1, '')) persisted;  -- or whatever

然后，您可以在它们上创建索引：

create index idx_table1_columns_123_notnull on table1(column1_notnull, column2_notnull, column3_notnull);

并重复table2.

然后，您的第一个查询将起作用(当然使用_notnull列(并利用索引来提高性能。

Answer 2

您可以使用SET ANSI_NULLS OFF=将两个null视为平等。

Answer 3

可以尝试以下方法：

DELETE t1
FROM Table1 t1
JOIN Table2 t2
ON 
Isnull(t1.[Column1],'') = isnull(t2.[Column1],'')
AND isnull(t1.[Column2],'') = isnull(t2.[Column2],'')
AND isnull(t1.[Column3],'') = isnull(t2.[Column3],'');