Categories:

Conditional Expression Functions

# EQUAL_NULL¶

Compares whether two expressions are equal. The function is NULL-safe, meaning it treats NULLs as known values for comparing equality. Note that this is different from the EQUAL comparison operator (`=`), which treats NULLs as unknown values.

IS [ NOT ] DISTINCT FROM

## Syntax¶

```EQUAL_NULL( <expr1> , <expr2> )
```

## Usage Notes¶

• The value returned depends on whether any of the inputs are NULL values:

Returns TRUE
`EQUAL_NULL( <null> , <null> )`
Returns FALSE
`EQUAL_NULL( <null> , <not_null> )`
`EQUAL_NULL( <not_null> , <null> )`

Otherwise:

`EQUAL_NULL(<expr1>, <expr2>)` is equivalent to `<expr1> = <expr2>`

For more details, see the examples below.

## Collation Details¶

• The collation specifications of all input arguments must be compatible.

• The comparisons follow the collation based on the input arguments’ collations and precedences.

## Examples¶

Create a table with simple data:

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

Show the Cartesian product generated by joining the table to itself without a filter:

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

Return rows that contain only equal values for both columns:

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

Return rows that contain only equal values or NULL values for both columns:

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

Illustrate all possible outcomes for EQUAL (`=`) and 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                                  |
|    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                                  |
+------+------+-----------------------+---------------------------------+----------------------------+--------------------------------------+
```
Language: English