标准 SQL 布尔运算符 IS 与等于 (=) 运算符 [英] Standard SQL boolean operator IS vs. equals (=) operator

问题描述

在 SQL 的维基百科页面上，有一些关于 SQL 中布尔逻辑的真值表.[1] 维基百科页面似乎来源于 SQL:2003 标准.

等于运算符 (=) 的真值表与 SQL:2003 草案中的 IS 运算符不同.

此外，维基百科文章指出IS NULL"()是一种特殊情况.

在 SQL:2003 中似乎有一个IS"操作符，它是一个像 AND、NOT 和 OR 这样的常规操作符.然而，<null predicate> 仍然存在.

当 IS 是常规布尔运算符时，为什么会出现 ?是否要确保您可以在没有类型强制的情况下使用具有非布尔值的IS NULL"构造?不鼓励使用=NULL"吗?

SQL:2011 标准的工作方式是否有所不同?

[1]:关于 SQL 的维基百科

[2]:SQL:2011 草案 PDF 第 335 页

[3]:SQL:2003 草案 PDF 第 397 页

解决方案

这对我来说是一个新方案.

如果我没看错的话， 语法定义了三个仅用于 boolean 数据类型 IS TRUE 的谓词，IS FALSE，未知.

它们与等式的不同之处在于它们的计算结果仅为 True 或 False.永远不要Unknown.即 UNKNOWN = TRUE 将评估为 UNKNOWN 但 UNKNOWN IS TRUE 评估为 False.

IS 和 = 的完整真值表如下.

+---------+-------+-------+---------+|是 |真|错误 |未知 |+---------+-------+-------+---------+|真|真|错误 |错误 ||错误 |错误 |真|错误 ||未知 |错误 |错误 |真|+---------+-------+-------+---------+

相对于

+---------+---------+---------+---------+|= |真|错误 |未知 |+---------+---------+---------+---------+|真|真|错误 |未知 ||错误 |错误 |真|未知 ||未知 |未知 |未知 |未知 |+---------+---------+---------+---------+

On the Wikipedia page for SQL there are some truth tables about boolean logic in SQL. [1] The Wikipedia page seems to source the SQL:2003 standard.

The truth table for the equals operator (=) is different from the IS operator from the SQL:2003 draft.

Also, the Wikipedia article notes that "IS NULL" (<null predicate>) is a special case.

In the SQL:2003 it seems that there is an "IS" opeartor which is a regular operator like AND, NOT and OR. However, the <null predicate> is still there.

Why is the <null predicate> there when the IS is a regular boolean operator? Is it to make sure you can use the "IS NULL" construct with non-boolean values without type coersion? Is it discouraged to use "=NULL"?

Does the SQL:2011 standard work differently?

[1]: Wikipedia on SQL

[2]: SQL:2011 draft PDF page 335

[3]: SQL:2003 draft PDF page 397

解决方案

That's a new one for me.

If I read that correctly the <boolean value expression> grammar defines three predicates solely for use with the boolean datatype IS TRUE, IS FALSE, IS UNKNOWN.

These differ from their equality counterparts in that they only evaluate to True or False. Never to Unknown. i.e. UNKNOWN = TRUE would evaluate to UNKNOWN but UNKNOWN IS TRUE evaluates to False.

The full truth tables for IS and = are below.

+---------+-------+-------+---------+
|   IS    | TRUE  | FALSE | UNKNOWN |
+---------+-------+-------+---------+
| TRUE    | TRUE  | FALSE | FALSE   |
| FALSE   | FALSE | TRUE  | FALSE   |
| UNKNOWN | FALSE | FALSE | TRUE    |
+---------+-------+-------+---------+

As opposed to

+---------+---------+---------+---------+
|    =    |  TRUE   |  FALSE  | UNKNOWN |
+---------+---------+---------+---------+
| TRUE    | TRUE    | FALSE   | UNKNOWN |
| FALSE   | FALSE   | TRUE    | UNKNOWN |
| UNKNOWN | UNKNOWN | UNKNOWN | UNKNOWN |
+---------+---------+---------+---------+

这篇关于标准 SQL 布尔运算符 IS 与等于 (=) 运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！