类别：: 条件表达式函数

[ NOT ] EQUAL_NULL¶

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

另请参阅:: IS [ NOT ] DISTINCT FROM

语法¶

[ NOT ] EQUAL_NULL( <expr1> , <expr2> )

使用说明¶

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

返回 TRUE:

EQUAL_NULL( <null> , <null> )

返回 FALSE:

EQUAL_NULL( <null> , <not_null> )

EQUAL_NULL( <not_null> , <null> )

否则：

EQUAL_NULL( <expr1>, <expr2>) 等效于 <expr1> = <expr2>

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

排序规则详细信息¶

The collation specifications of all input arguments must be compatible.
The comparisons follow the collation based on the input arguments' collations and precedences.

示例¶

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

CREATE OR REPLACE TABLE x (i NUMBER);
INSERT INTO x VALUES
  (1),
  (2),
  (NULL);

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

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 |
+------+------+

返回仅包含两列中相等值的行：

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 |
+------+------+

返回仅包含两列中相等值或 NULL 值的行：

SELECT x1.i x1_i, x2.i x2_i
  FROM x x1, x x2
  WHERE EQUAL_NULL(x1.i, x2.i);

+------+------+
| X1_I | X2_I |
|------+------|
|    1 |    1 |
|    2 |    2 |
| NULL | NULL |
+------+------+

展示 EQUAL (=) 和 NOT EQUAL (`` <>``) 的所有可能结果：

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                  |
+------+------+-----------+---------------------+------------+----------------------+

Illustrate all possible outcomes for EQUAL_NULL and NOT EQUAL_NULL:

SELECT x1.i x1_i,
       x2.i x2_i,
       EQUAL_NULL(x1.i, x2.i),
       IFF(EQUAL_NULL(x1.i, x2.i), 'Selected', 'Not') "SELECT IF EQUAL_NULL(X1.I,X2.I)",
       NOT EQUAL_NULL(x1.i, x2.i),
       IFF(NOT EQUAL_NULL(x1.i, x2.i), 'Selected', 'Not') "SELECT IF NOT(EQUAL_NULL(X1.I,X2.I))"
  FROM x x1, x x2;

+------+------+------------------------+---------------------------------+----------------------------+--------------------------------------+
| X1_I | X2_I | EQUAL_NULL(X1.I, X2.I) | SELECT IF EQUAL_NULL(X1.I,X2.I) | NOT EQUAL_NULL(X1.I, X2.I) | SELECT IF NOT(EQUAL_NULL(X1.I,X2.I)) |
|------+------+------------------------+---------------------------------+----------------------------+--------------------------------------|
|    1 |    1 | True                   | Selected                        | False                      | Not                                  |
|    2 |    1 | False                  | Not                             | True                       | Selected                             |
| NULL |    1 | False                  | Not                             | True                       | Selected                             |
|    1 |    2 | False                  | Not                             | True                       | Selected                             |
|    2 |    2 | True                   | Selected                        | False                      | Not                                  |
| NULL |    2 | False                  | Not                             | True                       | Selected                             |
|    1 | NULL | False                  | Not                             | True                       | Selected                             |
|    2 | NULL | False                  | Not                             | True                       | Selected                             |
| NULL | NULL | True                   | Selected                        | False                      | Not                                  |
+------+------+------------------------+---------------------------------+----------------------------+--------------------------------------+