Links

IS DISTINCT FROM and IS NOT DISTINCT FROM

IS DISTINCT FROM and IS NOT DISTINCT FROM

The IS DISTINCT FROM and IS NOT DISTINCT FROM operators compare two values and evaluates whether or not they are distinct from each other.
For example:
CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data MAP_COLUMNS_BY_NAME
SELECT
'a' IS DISTINCT FROM 'a' as distinct_example1, -- evaluates to false
'a' IS DISTINCT FROM 'b' as distinct_example2, -- evaluates to true
'a' IS NOT DISTINCT FROM 'a' as distinct_example3, -- evaluates to true
'a' IS NOT DISTINCT FROM 'b' as distinct_example4 -- evaluates to false
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE time_filter()
LIMIT 1;
Both operators work for all data types, provided that the two values being compared are of the same type.
In the above example, we compared a string to a string; however, if we had changed one of the columns in the query to be something like 'a' IS DISTINCT FROM 1, the query would fail as we cannot compare a string to an integer.

Comparing null values

In SQL a null value signifies an unknown value, so any comparison involving a null produces a null as well.
However, the IS DISTINCT FROM and IS NOT DISTINCT FROM operators treat null as a known value and both operators guarantee either a true or false outcome even in the presence of null input:
CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data MAP_COLUMNS_BY_NAME
SELECT
null::bigint IS DISTINCT FROM null::bigint as distinct_example1, -- evaluates to false
null::bigint IS NOT DISTINCT FROM null::bigint as distinct_example2, -- evaluates to true
3 IS DISTINCT FROM null::bigint as distinct_example3, -- evaluates to true
null::bigint IS NOT DISTINCT FROM 3 as distinct_example4 -- evaluates to false
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE time_filter()
LIMIT 1;
In the example shown above, a null value is considered not distinct from another null. When you are comparing values which may include null, use these operators to guarantee either a true or false result.
Note that the example above also casts null as a bigint since using an untyped null is not currently supported. The comparison result between null values remains the same true regardless of the data type the null is casted as.
However, when comparing two nulls, they should be casted as the same type; and when comparing a null with a non-null value, the null should be casted to match the data type of the non-null value
The following truth table demonstrate the handling of NULL in IS DISTINCT FROM and IS NOT DISTINCT FROM:
a
b
a = b
a <> b
a DISTINCT b
a NOT DISTINCT b
1
1
TRUE
FALSE
FALSE
TRUE
1
2
FALSE
TRUE
TRUE
FALSE
1
NULL
NULL
NULL
TRUE
FALSE
NULL
NULL
NULL
NULL
FALSE
TRUE
\