如何重写有别于和IS NOT有别于？如何重写有别于和IS NOT有别于？(How to rewrit

你如何改写包含标准表达式IS DISTINCT FROM和IS NOT DISTINCT FROM在SQL实现运营商，如Microsoft SQL Server 2008 R2不支持他们呢？

Answer 1:

该IS DISTINCT FROM谓词被引入作为SQL的功能T151：1999年，它的可读性否定， IS NOT DISTINCT FROM 2003年，加入作为SQL的功能T152。 这些谓词的目的是为了保证比较两个值的结果要么是真或假 ，从来不明 。

这些谓词与任何可比类型（包括行，列和多集），使得它相当复杂精确地模仿他们的工作。 但是，SQL Server不支持大多数类型的，所以我们可以通过检查空参数/操作数得到很远：

• a IS DISTINCT FROM b可以被改写为：

   ((a <> b OR a IS NULL OR b IS NULL) AND NOT (a IS NULL AND b IS NULL)) 

• a IS NOT DISTINCT FROM b可以改写为：

   (NOT (a <> b OR a IS NULL OR b IS NULL) OR (a IS NULL AND b IS NULL)) 

---

你自己的答案是不正确，因为它没有考虑到FALSE OR NULL的计算结果为未知 。 例如， NULL IS DISTINCT FROM NULL值应该为假 。 同样， 1 IS NOT DISTINCT FROM NULL值应该为假 。 在这两种情况下，你的表情产生未知 。

Answer 2:

我喜欢另一个解决方案利用的存在具有INTERSECT合并真实的二值布尔值结果。 这个解决方案应该在SQL Server 2005+工作。

• a IS NOT DISTINCT FROM b可以写为：

  EXISTS(SELECT a INTERSECT SELECT b)

如记录，INTERSECT对待两个空值如相等，所以如果两者都为NULL，则在单排INTERSECT结果，从而EXISTS产生true。

• a IS DISTINCT FROM b可以写为：

  NOT EXISTS(SELECT a INTERSECT SELECT b)

这种方法更加简洁，如果您有您需要在两个表来比较多个空列。 例如，为了返回在表B具有针对Col1中，Col2中，或比COL3 TableA中不同值的行，可以使用下列：

SELECT *
FROM TableA A
   INNER JOIN TableB B ON A.PK = B.PK
WHERE NOT EXISTS(
   SELECT A.Col1, A.Col2, A.Col3
   INTERSECT
   SELECT B.Col1, B.Col2, B.Col3);

保罗·怀特解释了这个解决办法的详细信息： http://web.archive.org/web/20180422151947/http://sqlblog.com:80/blogs/paul_white/archive/2011/06/22/undocumented-query-plans -equality-comparisons.aspx

Answer 3:

如果您的SQL执行不执行SQL标准IS DISTINCT FROM和IS NOT DISTINCT FROM运营商，你可以重写使用以下换算含有它们的表达式：

一般来说：

a IS DISTINCT FROM b <==>
(
    ((a) IS NULL AND (b) IS NOT NULL)
OR
    ((a) IS NOT NULL AND (b) IS NULL)
OR
    ((a) <> (b))
)

a IS NOT DISTINCT FROM b <==>
(
    ((a) IS NULL AND (b) IS NULL)
OR
    ((a) = (b))
)

在上下文中使用时，这个答案是不正确这里UNKNOWN和FALSE事项之间的区别。 我认为这是不常见的，虽然。 见@ChrisBandy接受的答案。

如果占位符值可被识别为没有实际发生中的数据，然后COALESCE是替代：

a IS DISTINCT FROM b <==> COALESCE(a, placeholder) <> COALESCE(b, placeholder)
a IS NOT DISTINCT FROM b <==> COALESCE(a, placeholder) = COALESCE(b, placeholder)

Answer 4:

在重写一个需要注意的是截然不同的和IS NOT不同于将不会使用索引的干扰，使用SQL Server时，至少。 换句话说，使用以下时：

WHERE COALESCE(@input, x) = COALESCE(column, x)

SQL Server将不能够使用包括列的所有指标。 因此，在WHERE子句，这将是最好使用形式

WHERE @input = column OR (@input IS NULL AND column IS NULL)

要为列的任何索引的优势。 （的括号仅用于清楚起见）

Answer 5:

对于参考，最经典的（和可读的）实施的IS [ NOT ] DISTINCT FROM将是一个阱格式化CASE表达。 为了IS DISTINCT FROM ：

CASE WHEN [a] IS     NULL AND [b] IS     NULL THEN FALSE
     WHEN [a] IS     NULL AND [b] IS NOT NULL THEN TRUE
     WHEN [a] IS NOT NULL AND [b] IS     NULL THEN TRUE
     WHEN [a] =               [b]             THEN FALSE
     ELSE                                          TRUE
END

显然，其它解决方案（特别是约翰·凯勒的 ，使用INTERSECT ）更为简洁。

更多详细的

Answer 6:

只是延长约翰·凯勒的答案。 我更喜欢使用EXISTS和EXCEPT模式：

a IS DISTINCT FROM b
<=>
EXISTS (SELECT a EXCEPT SELECT b)
-- NOT EXISTS (SELECT a INTERSECT SELECT b)

和

a IS NOT DISTINCT FROM  b
<=>
NOT EXISTS (SELECT a EXCEPT SELECT b)
-- EXISTS (SELECT a INTERSECT SELECT b)

一个特别的原因。 NOT而与对齐INTERSECT它反转。

---

SELECT 1 AS PK, 21 AS c, NULL  AS  b
INTO tab1;

SELECT 1 AS PK, 21 AS c, 2 AS b
INTO tab2;

SELECT *
FROM tab1 A
JOIN tab2 B ON A.PK = B.PK
WHERE EXISTS(SELECT A.c, A.B
              EXCEPT
              SELECT B.c, B.b);

DBFiddle演示

Answer 7:

These expressions can be a good substitute for the IS DISTINCT FROM logic and perform better than the previous examples because they end up being compiled by SQL server into a single predicate expression which will result in approx. half the operator cost on a filter expression. They are essentially the same as the solutions as provided by Chris Bandy, however they use nested ISNULL and NULLIF functions to perform the underlying comparisons.

(... obviously ISNULL could be substituted with COALESCE if you prefer)

• a IS DISTINCT FROM b can be rewritten as:

  ISNULL(NULLIF(a, b), NULLIF(b, a)) IS NOT NULL

• a IS NOT DISTINCT FROM b can be rewritten as:

  ISNULL(NULLIF(a, b), NULLIF(b, a)) IS NULL

Answer 8:

a IS NOT DISTINCT FROM b

可改写为：

(a IS NOT NULL AND b IS NOT NULL AND a=b) OR (a IS NULL AND b is NULL)

---

a IS DISTINCT FROM b

可改写为：

NOT (a IS NOT DISTINCT FROM b)

Answer 9:

这是一个老问题，有一个新的答案。 这是比较容易理解和维护。

-- a IS DISTINCT FROM b
CASE WHEN (a = b) OR (a IS NULL AND b IS NULL) THEN 1 ELSE 0 END = 0

-- a IS NOT DISTINCT FROM b
CASE WHEN (a = b) OR (a IS NULL AND b IS NULL) THEN 1 ELSE 0 END = 1

应当指出的是，这句法替代IS [NOT] DISTINCT FROM在所有主要的SQL数据库工程（见末链接）。 这和替代品精心解释这里

文章来源: How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM?