Links

Logical operators

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 time_filter()
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 time_filter()
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 time_filter()
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