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.

See also: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                                  |
+------+------+-----------------------+---------------------------------+----------------------------+--------------------------------------+