IS NULL and IS NOT NULL

The IS NULL and IS NOT NULL operators test whether or nota value is null (undefined).

Both operators work for all data types.

IS NULL

Using null with IS NULL evaluates to true:

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 NULL as null_example -- evaluates to true
    FROM default_glue_catalog.upsolver_samples.orders_raw_data 
    WHERE time_filter()
    LIMIT 1;

Note that the example above casts null as a bigint since using an untyped null is not currently supported. The comparison result, however, is the same true regardless of the data type the null is casted as.

For any value other than null, IS NULL evaluates to false :

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 
        3.0 IS NULL as null_example -- evaluates to false
    FROM default_glue_catalog.upsolver_samples.orders_raw_data 
    WHERE time_filter()
    LIMIT 1;

IS NOT NULL

The inverse of IS NULL is IS NOT NULL; thus, using null with IS NOT NULL evaluates to false:

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 NOT NULL as null_example -- evaluates to false
    FROM default_glue_catalog.upsolver_samples.orders_raw_data 
    WHERE time_filter()
    LIMIT 1;

For any value other than null, IS NOT NULL evaluates to true :

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 
        3.0 IS NOT NULL as null_example -- evaluates to true
    FROM default_glue_catalog.upsolver_samples.orders_raw_data 
    WHERE time_filter()
    LIMIT 1;