Links

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;