Logical

Logical operators

Operator	Description	Example
AND	True if both values are true	a AND b
OR	True if either value is true	a OR b
NOT	True if the value is false	NOT a

Effect of NULL on logical operators

AND

The result of an AND comparison may be NULL if one or both sides of the expression are NULL.

If, however, at least one side of an AND operator is FALSE, then the expression evaluates to FALSE.

Transformation job example

SQL

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::boolean AND true AS and_example1,
        null::boolean AND false AS and_example2,
        null::boolean AND null::boolean AS and_example3
    FROM default_glue_catalog.upsolver_samples.orders_raw_data 
    WHERE $commit_time BETWEEN run_start_time() AND run_end_time()
    LIMIT 1;

Note that the example above casts null as a Boolean since using an untyped null is not currently supported and logical operators expect Boolean inputs.

Query result:

and_example1	and_example2	and_example3
null	false	null

OR

The result of an OR comparison may be NULL if one or both sides of the expression are NULL.

If, however, at least one side of an OR operator is TRUE the expression evaluates to TRUE.

Transformation job example

SQL

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::boolean OR null::boolean AS or_example1,
        null::boolean OR false AS or_example2,
        null::boolean OR true AS or_example3
    FROM default_glue_catalog.upsolver_samples.orders_raw_data 
    WHERE $commit_time BETWEEN run_start_time() AND run_end_time()
    LIMIT 1;

Query result:

or_example1	or_example2	or_example3
null	null	true

AND and OR truth table

The following truth table demonstrates the handling of NULL in AND and OR:

a	b	a AND b	a OR b
TRUE	TRUE	TRUE	TRUE
TRUE	FALSE	FALSE	TRUE
TRUE	NULL	NULL	TRUE
FALSE	TRUE	FALSE	TRUE
FALSE	FALSE	FALSE	FALSE
FALSE	NULL	FALSE	NULL
NULL	TRUE	NULL	TRUE
NULL	FALSE	FALSE	NULL
NULL	NULL	NULL	NULL

NOT

The logical complement of NULL is NULL as shown in the following example with a transformation job.

SQL

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 
        NOT null::boolean AS not_example
    FROM default_glue_catalog.upsolver_samples.orders_raw_data 
    WHERE $commit_time BETWEEN run_start_time() AND run_end_time()
    LIMIT 1;

Query result:

not_example
null

NOT truth table

The following truth table demonstrates the handling of NULL in NOT:

a	NOT a
TRUE	FALSE
FALSE	TRUE
NULL	NULL