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

\