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

customer_email
[e-mail]
[e-mail]
[e-mail]

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

customer_email
[e-mail]
[e-mail]
[e-mail]