有没有办法简化 2 个值的 NULL 比较 [英] Is there a way to simplify a NULL compare of 2 values

问题描述

这是我的简单陈述

SELECT ...
FROM tab1 AS i FULL OUTER JOIN tab2 AS d ON i.[Id]=d.[Id] 
WHERE d.[Data]<>i.[Data] OR 
    (d.[Data] IS NULL AND i.[Data] IS NOT NULL) OR 
    (d.[Data] IS NOT NULL AND i.[Data] IS NULL)

我想获取所有的条目

1. i.[Data] 不同于 d.[Data]
2. 表 i 或 d 中至少有一个值为 NOT NULL

所以我不想看到记录 was 和 i 和 d 包含相同的数据或者都是 NULL.

So I don't want to see records were and i and d contain the same data or are both NULL.

我的陈述看起来又长又复杂.有没有更简单的方法?

My statement look so long and complicated. Is there an easier way?

使用 ISNULL(d.[Data],'')<>ISNULL(i.[Data],'') 适用于文本，但不适用于 DATE 或 TIME(0) 列.

Using ISNULL(d.[Data],'')<>ISNULL(i.[Data],'') works for text, but not for DATE or TIME(0) columns.

我的语句适用于所有类型.

My statement works for every type.

推荐答案

是的，你可以，而且你也可以让优化器识别它.

保罗怀特有这个小曲子:

WHERE NOT EXISTS (
    SELECT d.[Data]
    INTERSECT
    SELECT i.[Data])

这是因为 INTERSECT 处理空值的语义.这意味着在由值 B 和值 B 组成的子查询中是否有没有行"，只有当它们是不同的值或者一个为空而另一个不是时才会满足.如果两者都是空值，则会有一行为空值.

This works because of the semantics of INTERSECT which deal with nulls. What this says is "are there no rows in the subquery made up of value B and value B", this will only be satisfied if they are different values or one is null and the other not. If both are nulls, there will be a row with a null.

如果您检查 XML 查询计划(不是 SSMS 中的图形查询计划)，您将看到它一直编译到 d.[Data] <>i.[Data]，但它使用的运算符将具有 CompareOp="IS" 而不是 EQ.

If you check the XML query plan (not the graphical one in SSMS), you will see that it compiles all the way down to d.[Data] <> i.[Data], but the operator it uses will have CompareOp="IS" and not EQ.

在此处查看完整计划.

该计划的相关部分是:

                <Predicate>
                  <ScalarOperator ScalarString="@t1.[i] as [t1].[i] = @t2.[i] as [t2].[i]">
                    <Compare CompareOp="IS">
                      <ScalarOperator>
                        <Identifier>
                          <ColumnReference Table="@t1" Alias="[t1]" Column="i" />
                        </Identifier>
                      </ScalarOperator>
                      <ScalarOperator>
                        <Identifier>
                          <ColumnReference Table="@t2" Alias="[t2]" Column="i" />
                        </Identifier>
                      </ScalarOperator>
                    </Compare>
                  </ScalarOperator>
                </Predicate>

我发现优化器以这种方式运行得非常好，而不是执行 EXISTS/EXCEPT.

I find the optimizer works very well this way round, rather than doing EXISTS / EXCEPT.

我敦促您投票支持 Azure 反馈 以实现适当的运算符

I urge you to vote for the Azure Feedback to implement a proper operator

这篇关于有没有办法简化 2 个值的 NULL 比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！