CODEWITHSUNDEEP
sqlwhere-clausesnowflake-cloud-data-platformsql-null
Marty C.
Marty C.

Reputation: 696

Difference between x = null vs. x IS NULL

In Snowflake, what is the difference between x = NULL and x IS NULL in a condition expression? It seems empirically that x IS NULL is what I want when I want to find rows where some column is blank. I ask because x = NULL is treated as valid syntax and I am curious whether there's a different application for this expression.

Upvotes: 4

Views: 2599

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 175716

This particular case is well-described in Snowflake's documentation:

EQUAL_NULL

IS [ NOT ] DISTINCT FROM

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.

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

Upvotes: 1

David Garrison
David Garrison

Reputation: 2880

Like most SQL languages, comparing NULL = NULL does not return TRUE. In SnowFlake, it returns NULL, as does ANY comparison to a NULL value. The reason for this is tied to the convoluted history of SQL, and it has been well argued whether or not this is a good feature or not. Regardless, it's what we have.

As such, when you are comparing two values that may be NULL here are a few different solutions you can typically use.

-- NVL will return the second value if the first value is NULL
-- So if both of your values are NULL, then an NVL around each of them will 
-- return a value so that they are both equal.
-- This only works if you know that your values will never be equal to -1 for example
SELECT ...
WHERE NVL(x, -1) = NVL(y, -1)

-- A little messier, especially among more complicated filters,
-- but guaranteed to work regardless of values
SELECT ...
WHERE x = y OR (x is null and y is null)

-- My new favorite which works in SnowFlake (thanks to @waldente)
SELECT x IS NOT DISTINCT FROM y;

-- For most SQL languages, this is a neat way to take advantage of how 
-- INTERSECT compares values which does treat NULLs as equal
SELECT ...
WHERE exists (select x intersect select y)

Upvotes: 1

GMB
GMB

Reputation: 222482

what is the difference between x = NULL and x IS NULL

In Snowflake just like in other RDBMS, Nothing is equal to NULL (even NULL itself), so a condition x = NULL (which is valid SQL syntax) will always evaluate as false (well, actually, it evaluates to NULL in most RDBMS, which is not true). Note that this is also true for non-equality comparisons: that is NULL <> NULL is false too.

The typical way to check if a variable is NULL is to use the x IS NULL construct, which evaluate as true if x is NULL. You can use x IS NOT NULL too. This syntax is reserved for NULL, so something like x IS y is a syntax error.

Here is a small demo:

select 
    case when 1 = null then 1 else 0 end 1_equal_null,
    case when 1 <> null then 1 else 0 end 1_not_equal_null,
    case when null is null then 1 else 0 end null_is_null,
    case when 1 is not null then 1 else 0 end 1_is_not_null

1_equal_null | 1_not_equal_null | null_is_null | 1_is_not_null
-----------: | ---------------: | -----------: | ------------:
           0 |                0 |            1 |             1

Upvotes: 6

Related Questions