如何重写 IS DISTINCT FROM 和 IS NOT DISTINCT FROM? [英] How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM?

问题描述

如何在不支持它们的 SQL 实现(例如 Microsoft SQL Server 2008R2)中重写包含标准 IS DISTINCT FROM 和 IS NOT DISTINCT FROM 运算符的表达式?

How do you rewrite expressions containing the standard IS DISTINCT FROM and IS NOT DISTINCT FROM operators in SQL implementations such as Microsoft SQL Server 2008R2 that do not support them?

推荐答案

IS DISTINCT FROM 谓词作为 SQL:1999 的特性 T151 引入，它的可读否定，IS NOT DISTINCTFROM，作为 SQL:2003 的特性 T152 添加.这些谓词的目的是保证比较两个值的结果要么是True，要么是False，永远不会Unknown.

The IS DISTINCT FROM predicate was introduced as feature T151 of SQL:1999, and its readable negation, IS NOT DISTINCT FROM, was added as feature T152 of SQL:2003. The purpose of these predicates is to guarantee that the result of comparing two values is either True or False, never Unknown.

这些谓词适用于任何可比较的类型(包括行、数组和多重集)，这使得精确模拟它们变得相当复杂.但是，SQL Server 不支持大多数这些类型，因此我们可以通过检查空参数/操作数来走得更远:

These predicates work with any comparable type (including rows, arrays and multisets) making it rather complicated to emulate them exactly. However, SQL Server doesn't support most of these types, so we can get pretty far by checking for null arguments/operands:

• 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 的计算结果为 Unknown.例如，NULL IS DISTINCT FROM NULL 应评估为 False.类似地，1 IS NOT DISTINCT FROM NULL 应评估为 False.在这两种情况下，您的表达式都会产生 Unknown.

Your own answer is incorrect as it fails to consider that FALSE OR NULL evaluates to Unknown. For example, NULL IS DISTINCT FROM NULL should evaluate to False. Similarly, 1 IS NOT DISTINCT FROM NULL should evaluate to False. In both cases, your expressions yield Unknown.

这篇关于如何重写 IS DISTINCT FROM 和 IS NOT DISTINCT FROM?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！