类别：: 条件表达式函数

IS [ NOT ] DISTINCT FROM¶

比较两个表达式是否相等（或不相等）。该函数为 NULL-safe，意味着它将 NULLs 视作用于比较相等性的已知值。请注意，这与 EQUAL 比较运算符 (=) 不同，后者将 NULLs 视为未知值。

另请参阅：: EQUAL_NULL

语法¶

<expr1> IS [ NOT ] DISTINCT FROM <expr2>

Copy

使用说明¶

返回的值取决于是否有输入为 NULL 值：

返回 TRUE:

<null> IS NOT DISTINCT FROM <null>

<null> IS DISTINCT FROM <not_null>

<not_null> IS DISTINCT FROM <null>

返回 FALSE:

<null> IS DISTINCT FROM <null>

<null> IS NOT DISTINCT FROM <not_null>

<not_null> IS NOT DISTINCT FROM <null>

否则：

<expr1> IS DISTINCT FROM <expr2> 等同于 <expr1> != <expr2>

<expr1> IS NOT DISTINCT FROM <expr2> 等同于 <expr1> = <expr2>

有关详细信息，请参阅以下示例。

示例¶

创建一个包含简单数据的表：

CREATE OR REPLACE TABLE x (i number);
INSERT INTO x values
    (1), 
    (2), 
    (null);

Copy

展示通过将表联接到自身而生成的笛卡尔积，而不使用筛选器：

SELECT x1.i x1_i, x2.i x2_i 
    FROM x x1, x x2
    ORDER BY x1.i, x2.i;
+------+------+
| X1_I | X2_I |
|------+------|
|    1 |    1 |
|    1 |    2 |
|    1 | NULL |
|    2 |    1 |
|    2 |    2 |
|    2 | NULL |
| NULL |    1 |
| NULL |    2 |
| NULL | NULL |
+------+------+

Copy

返回包含以下内容的行：

仅限两列中的相等值。
仅限两列中的相等值或 NULL 值。

SELECT x1.i x1_i, x2.i x2_i 
    FROM x x1, x x2 
    WHERE x1.i=x2.i;
+------+------+
| X1_I | X2_I |
|------+------|
|    1 |    1 |
|    2 |    2 |
+------+------+

Copy

SELECT x1.i x1_i, x2.i x2_i 
    FROM x x1, x x2 
    WHERE x1.i IS NOT DISTINCT FROM x2.i
    ORDER BY x1.i;
+------+------+
| X1_I | X2_I |
|------+------|
|    1 |    1 |
|    2 |    2 |
| NULL | NULL |
+------+------+

Copy

展示以下内容中所有可能的结果：

EQUAL = 和 NOT EQUAL <>
IS NOT DISTINCT FROM 和 IS DISTINCT FROM

SELECT x1.i x1_i, 
       x2.i x2_i,
       x1.i=x2.i, 
       iff(x1.i=x2.i, 'Selected', 'Not') "SELECT IF X1.I=X2.I",
       x1.i<>x2.i, 
       iff(not(x1.i=x2.i), 'Selected', 'Not') "SELECT IF X1.I<>X2.I"
    FROM x x1, x x2;
+------+------+-----------+---------------------+------------+----------------------+
| X1_I | X2_I | X1.I=X2.I | SELECT IF X1.I=X2.I | X1.I<>X2.I | SELECT IF X1.I<>X2.I |
|------+------+-----------+---------------------+------------+----------------------|
|    1 |    1 | True      | Selected            | False      | Not                  |
|    1 |    2 | False     | Not                 | True       | Selected             |
|    1 | NULL | NULL      | Not                 | NULL       | Not                  |
|    2 |    1 | False     | Not                 | True       | Selected             |
|    2 |    2 | True      | Selected            | False      | Not                  |
|    2 | NULL | NULL      | Not                 | NULL       | Not                  |
| NULL |    1 | NULL      | Not                 | NULL       | Not                  |
| NULL |    2 | NULL      | Not                 | NULL       | Not                  |
| NULL | NULL | NULL      | Not                 | NULL       | Not                  |
+------+------+-----------+---------------------+------------+----------------------+

Copy

SELECT x1.i x1_i, x2.i x2_i,
               x1.i IS NOT DISTINCT FROM x2.i, iff(x1.i IS NOT DISTINCT FROM x2.i, 'Selected', 'Not') "SELECT IF X1.I IS NOT DISTINCT FROM X2.I",
               x1.i IS DISTINCT FROM x2.i, iff(x1.i IS DISTINCT FROM x2.i, 'Selected', 'Not') "SELECT IF X1.I IS DISTINCT FROM X2.I"
        FROM x x1, x x2
        ORDER BY x1.i, x2.i;
+------+------+--------------------------------+------------------------------------------+----------------------------+--------------------------------------+
| X1_I | X2_I | X1.I IS NOT DISTINCT FROM X2.I | SELECT IF X1.I IS NOT DISTINCT FROM X2.I | X1.I IS DISTINCT FROM X2.I | SELECT IF X1.I IS DISTINCT FROM X2.I |
|------+------+--------------------------------+------------------------------------------+----------------------------+--------------------------------------|
|    1 |    1 | True                           | Selected                                 | False                      | Not                                  |
|    1 |    2 | False                          | Not                                      | True                       | Selected                             |
|    1 | NULL | False                          | Not                                      | True                       | Selected                             |
|    2 |    1 | False                          | Not                                      | True                       | Selected                             |
|    2 |    2 | True                           | Selected                                 | False                      | Not                                  |
|    2 | NULL | False                          | Not                                      | True                       | Selected                             |
| NULL |    1 | False                          | Not                                      | True                       | Selected                             |
| NULL |    2 | False                          | Not                                      | True                       | Selected                             |
| NULL | NULL | True                           | Selected                                 | False                      | Not                                  |
+------+------+--------------------------------+------------------------------------------+----------------------------+--------------------------------------+

Copy