Logical

Logical operators

OperatorDescriptionExample

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_example1and_example2and_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_example1or_example2or_example3

null

null

true

AND and OR truth table

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

aba AND ba 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:

aNOT a

TRUE

FALSE

FALSE

TRUE

NULL

NULL

Last updated