Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://docs.snowflake.com/sql-reference/functions/equal_null below:

Website Navigation

• Reference
• Function and stored procedure reference
• Conditional expression

EQUAL_NULL | Snowflake Documentation

Categories:

Conditional expression functions

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

EQUAL_NULL( <expr1> , <expr2> )

Copy

• 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.

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

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

Create a table with simple data:

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4