本文属于机器翻译版本。若本译文内容与英语原文存在差异，则一律以英文原文为准。

# 逻辑运算符
<a name="sql-reference-logical-operators"></a>

逻辑运算符允许您创建条件并测试其结果。


| 运算符 | 一元/二进制 | 说明 | 运算对象 | 
| --- | --- | --- | --- | 
| NOT | U | 逻辑求反 | 布尔值 | 
| AND | B | 连词 | 布尔值 | 
| 或 | B | 析取 | 布尔值 | 
| IS | B | 逻辑断言 | 布尔值 | 
| IS NOT UNKNOWN | U | 否定未知比较：<br /><expr> IS NOT UNKNOWN | 布尔值 | 
| IS NULL | U | Null 比较：<br /><expr> IS NULL | Any | 
| IS NOT NULL | U | 否定 null 比较：<br /><expr> IS NOT NULL | Any | 
| = | B | 等于 | Any | 
| \!= | B | 不等于 | Any | 
| <> | B | 不等于 | Any | 
| > | B | Greater than | 有序类型（数字、字符串、日期、时间） | 
| >= | B | 大于或等于（不小于） | 有序类型 | 
| < | B | Less than | 有序类型 | 
| <= | B | 小于或等于（不大于） | 有序类型 | 
| BETWEEN | 三元 | 范围比较：<br />col1 BETWEEN expr1 AND expr2 | 有序类型 | 
| IS DISTINCT FROM | B | 区别 | Any | 
| IS NOT DISTINCT FROM | B | 否定区别 | Any | 

## 三态布尔逻辑
<a name="three_state_boolean_logic"></a>

SQL 布尔值有三种可能的状态，而不是通常的两种状态：TRUE、FALSE 和 UNKNOWN，其中最后一种等同于布尔值 NULL。TRUE 和 FALSE 运算对象通常根据普通的双态布尔逻辑起作用，但是在将它们与 UNKNOWN 运算对象配对时会适用其他规则，如下表所示。

**注意**  
UNKOWN 表示“可能是 TRUE，也可能是 FALSE”，或者换句话说，“不绝对是 TRUE，也不绝对是 FALSE”。这种理解可以帮助您弄清为什么表中的某些表达式会这样计算。


**求反 (NOT)**  

| 操作 | 结果 | 
| --- | --- | 
| NOT TRUE | FALSE | 
| NOT FALSE | TRUE | 
| NOT UNKNOWN | UNKNOWN | 


**连词 (AND)**  

| 操作 | 结果 | 
| --- | --- | 
| TRUE AND TRUE | TRUE | 
| TRUE AND FALSE | FALSE | 
| TRUE AND UNKNOWN | UNKNOWN | 
| FALSE AND TRUE | FALSE | 
| FALSE AND FALSE | FALSE | 
| FALSE AND UNKNOWN | FALSE | 
| UNKNOWN AND TRUE | UNKNOWN | 
| UNKNOWN AND FALSE | FALSE | 
| UNKNOWN AND UNKNOWN | UNKNOWN | 


**析取 (OR)**  

| 操作 | 结果 | 
| --- | --- | 
| TRUE OR TRUE | TRUE | 
| TRUE OR FALSE | TRUE | 
| TRUE OR UNKNOWN | TRUE | 
| FALSE OR TRUE | TRUE | 
| FALSE OR FALSE | FALSE | 
| FALSE OR UNKNOWN | UNKNOWN | 
| UNKNOWN OR TRUE | TRUE | 
| UNKNOWN OR FALSE | UNKNOWN | 
| UNKNOWN OR UNKNOWN | UNKNOWN | 


**断言 (IS)**  

| 操作 | 结果 | 
| --- | --- | 
| TRUE IS TRUE | TRUE | 
| TRUE IS FALSE | FALSE | 
| TRUE IS UNKNOWN | FALSE | 
| FALSE IS TRUE | FALSE | 
| FALSE IS FALSE | TRUE | 
| FALSE IS UNKNOWN | FALSE | 
| UNKNOWN IS TRUE | FALSE | 
| UNKNOWN IS FALSE | FALSE | 
| UNKNOWN IS UNKNOWN | TRUE | 


**IS NOT UNKNOWN**  

| 操作 | 结果 | 
| --- | --- | 
| TRUE IS NOT UNKNOWN | TRUE | 
| FALSE IS NOT UNKNOWN | TRUE | 
| UNKNOWN IS NOT UNKNOWN | FALSE | 

IS NOT UNKNOWN 本身就是一个特殊的运算符。表达式“x IS NOT UNKNOWN”等同于“(x IS TRUE) OR (x IS FALSE)”，而不是“x IS (NOT UNKNOWN)”。因此，在上表中替换：


| x | 操作 | 结果 |   | 在“(x IS TRUE) OR (x IS FALSE)”中替换 x 的结果 | 
| --- | --- | --- | --- | --- | 
| TRUE | TRUE IS NOT UNKNOWN | TRUE | 变为 | “(TRUE IS TRUE) OR (TRUE IS FALSE)”，即 TRUE | 
| FALSE | FALSE IS NOT UNKNOWN | TRUE | 变为 | “(FALSE IS TRUE) OR (FALSE IS FALSE)”，即 TRUE | 
| UNKNOWN | UNKNOWN IS NOT UNKNOWN | FALSE | 变为 | “(UNKNOWN IS TRUE) OR (UNKNOWN IS FALSE)”，即 FALSE，<br />因为 UNKNOWN 既不是 TRUE 也不是 FALSE | 

由于 IS NOT UNKNOWN 是一个特殊的运算符，因此上述运算在 IS 这个词周围是不可传递的：


| 操作 | 结果 | 
| --- | --- | 
| NOT UNKNOWN IS TRUE | FALSE | 
| NOT UNKNOWN IS FALSE | FALSE | 
| NOT UNKNOWN IS UNKNOWN | TRUE | 


**IS NULL 和 IS NOT NULL**  

| 操作 | 结果 | 
| --- | --- | 
| UNKNOWN IS NULL | TRUE | 
| UNKNOWN IS NOT NULL | FALSE | 
| NULL IS NULL | TRUE | 
| NULL IS NOT NULL | FALSE | 


**IS DISTINCT FROM 和 IS NOT DISTINCT FROM**  

| 操作 | 结果 | 
| --- | --- | 
| UNKNOWN IS DISTINCT FROM TRUE | TRUE | 
| UNKNOWN IS DISTINCT FROM FALSE | TRUE | 
| UNKNOWN IS DISTINCT FROM UNKNOWN | FALSE | 
| UNKNOWN IS NOT DISTINCT FROM TRUE | FALSE | 
| UNKNOWN IS NOT DISTINCT FROM FALSE | FALSE | 
| UNKNOWN IS NOT DISTINCT FROM UNKNOWN | TRUE | 



通常，“x IS DISTINCT FROM y”类似于“x <> y”，在 x 或 y（而非二者）为 NULL 时也为 true 的情况除外。DISTINCT FROM 与“相同”相反，后者的通常含义是值（true、false 或 unknown）与其自身相同，并且与其他所有值不同。IS 和 IS NOT 运算符以一种特殊的方式处理 UNKOWN，因为它表示“可能是 TRUE，也许是 FALSE”。

## 其他逻辑运算符
<a name="w2aac10c19c25b9"></a>

对于所有其他运算符，传递 NULL 或 UNKNOWN 运算对象将导致结果为 UNKNOWN（与 NULL 相同）。


**示例**  

| 操作 | 结果 | 
| --- | --- | 
| TRUE AND CAST( NULL AS BOOLEAN) | UNKNOWN | 
| FALSE AND CAST( NULL AS BOOLEAN) | FALSE | 
| 1 > 2 | FALSE | 
| 1 < 2 | TRUE | 
| 'foo' = 'bar' | FALSE | 
| 'foo' <> 'bar' | TRUE | 
| 'foo' <= 'bar' | FALSE | 
| 'foo' <= 'bar' | TRUE | 
| 3 BETWEEN 1 AND 5 | TRUE | 
| 1 BETWEEN 3 AND 5 | FALSE | 
| 3 BETWEEN 3 AND 5 | TRUE | 
| 5 BETWEEN 3 AND 5 | TRUE | 
| 1 IS DISTINCT FROM 1.0 | FALSE | 
| CAST( NULL AS INTEGER ) IS NOT DISTINCT FROM CAST (NULL AS INTEGER) | TRUE |