# IN

The

`IN`

operator compares a value to a given list of values to determine if there is a match found within that list.<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.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;

customer_email |
---|

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.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;

customer_email |
---|

Last modified 6mo ago