如何在 SQL Server 2008R2 中重写 IS DISTINCT FROM 和 IS NOT DISTINCT FROM 运算符？

在 SQL Server 2008R2 中重写 IS DISTINCT FROM 运算符

SQL Server 2008R2 不支持标准的 IS DISTINCT FROM 和 IS NOT DISTINCT FROM 运算符。本文提供了一种解决方案，使用与该软件版本兼容的替代语法来重写这些表达式。

IS DISTINCT FROM

如果两个值不相等或其中一个值为 NULL，则 IS DISTINCT FROM 谓词计算结果为 True；如果它们相等且都不为 NULL，则计算结果为 False。它可以改写如下：

<code class="language-sql">((a != b OR a IS NULL OR b IS NULL) AND NOT (a IS NULL AND b IS NULL))</code>

示例：

<code class="language-sql">SELECT CASE
    WHEN (a != b OR a IS NULL OR b IS NULL) AND NOT (a IS NULL AND b IS NULL) THEN 'True'
    ELSE 'False'
END AS DistinctResult</code>

IS NOT DISTINCT FROM

IS NOT DISTINCT FROM 运算符在两个值相等或都为 NULL 时计算结果为 True，在它们不相等且不都为 NULL 时计算结果为 False。它可以改写为：

<code class="language-sql">(NOT (a != b OR a IS NULL OR b IS NULL) OR (a IS NULL AND b IS NULL))</code>

示例：

<code class="language-sql">SELECT CASE
    WHEN (NOT (a != b OR a IS NULL OR b IS NULL) OR (a IS NULL AND b IS NULL)) THEN 'True'
    ELSE 'False'
END AS NotDistinctResult</code>

注意：

提供的重写确保结果始终为 True 或 False，避免了带有 NULL 操作数时可能出现的 Unknown 状态。

以上是如何在 SQL Server 2008R2 中重写 IS DISTINCT FROM 和 IS NOT DISTINCT FROM 运算符？的详细内容。更多信息请关注PHP中文网其他相关文章！