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

问题描述

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

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

此外，Wikipedia文章还指出"IS NULL"()是一种特殊情况.

在SQL:2003中，似乎有一个"IS"操作符，它是一个常规运算符，例如AND，NOT和OR.但是，< null谓词>仍然存在.

当IS是常规布尔运算符时，为什么在< null谓词>那里?是否要确保您可以将带有非布尔值的"IS NULL"结构用于类型强制转换?不鼓励使用"= NULL"吗?

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

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

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

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

解决方案

对我来说这是一个新的东西.

如果我没看错的话，<boolean value expression>语法定义了三个谓词，仅可用于boolean数据类型IS TRUE，IS FALSE，IS UNKNOWN.

这些与等式的不同之处在于它们仅求值为True或False.永远不要去Unknown.即UNKNOWN = TRUE的评估结果为UNKNOWN，而UNKNOWN IS TRUE的评估结果为False.

以下是IS和=的完整真相表.

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

与

相反

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

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屋！