Links

IN

IN

The IN operator compares a value to a given list of values to determine if there is a match found within that list.

Syntax

<expression> IN (<value> [, ...])
The right-hand side is a parenthesized list of scalar expressions. The result is true if the left-hand expression's result is equal to any of the right-hand expressions.
This is a shorthand notation for:
<expression> = <value1>
OR
<expression> = <value2>
OR
...
Note that if the left-hand expression yields null, or if there are no equivalent right-hand values and at least one right-hand expression yields null, the result of the IN construct is null, not false. This is in accordance with SQL's normal rules for Boolean combinations of null values.

Examples

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
customer_email
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE (time_filter())
AND ELEMENT_AT(SPLIT(customer_email, '@'), 2) IN ('gmail.com', 'yahoo.com')
LIMIT 3;

Query result

NOT IN

expression NOT IN (value [, ...])
The right-hand side is a parenthesized list of scalar expressions. The result is true if the left-hand expression's result is unequal to all of the right-hand expressions.
This is a shorthand notation for:
<expression> != <value1>
AND
<expression> != <value2>
AND
...
Note that if the left-hand expression yields null, or if there are no equal right-hand values and at least one right-hand expression yields null, the result of the NOT IN construct will be null, not true. This is in accordance with SQL's normal rules for Boolean combinations of null values.
Note that x NOT IN y is equivalent to NOT (x IN y) in all cases.
However, users tend to make more mistakes with null values when working with NOT IN than when working with IN. As such, it is best to express your condition positively if possible.

Examples

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
customer_email
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE (time_filter())
AND ELEMENT_AT(SPLIT(customer_email, '@'), 2) NOT IN ('gmail.com', 'yahoo.com')
LIMIT 3;

Query result