IN¶

Tests whether a value matches any value in a list.

Syntax¶

The IN operator is used in WHERE clauses:

SELECT column1, column2
FROM table_name
WHERE column1 IN (value1, value2, value3)

Parameters¶

expression: The value to test. Can be a column name, calculation, or any valid expression.
value1, value2, ...: A comma-separated list of values to compare against. Values must be of compatible types with the expression.
NOT (optional): Negates the result - returns true if the expression does not match any value in the list.

Returns¶

Returns:

TRUE if the expression equals any value in the list
FALSE if the expression does not match any value in the list
NULL if:
- The expression is NULL, OR
- The expression doesn’t match any non-NULL value AND the list contains at least one NULL

Examples¶

Setup¶

For these examples, assume we have a products table:

CREATE TABLE products(
    id BIGINT,
    name STRING,
    category STRING,
    price BIGINT,
    PRIMARY KEY(id))

INSERT INTO products VALUES
    (1, 'Widget A', 'Electronics', 100),
    (2, 'Widget B', 'Electronics', 150),
    (3, 'Gadget X', 'Electronics', 200),
    (4, 'Tool A', 'Hardware', 80),
    (5, 'Tool B', 'Hardware', 120),
    (6, 'Book A', 'Media', 25),
    (7, 'Book B', 'Media', 30)

IN with Numbers¶

Find products with specific IDs:

SELECT name, price
FROM products
WHERE id IN (1, 3, 5)

`name`	`price`
`"Widget A"`	`100`
`"Gadget X"`	`200`
`"Tool B"`	`120`

IN with Strings¶

Find products in specific categories:

SELECT name, category
FROM products
WHERE category IN ('Electronics', 'Media')

`name`	`category`
`"Widget A"`	`"Electronics"`
`"Widget B"`	`"Electronics"`
`"Gadget X"`	`"Electronics"`
`"Book A"`	`"Media"`
`"Book B"`	`"Media"`

NOT IN¶

Find products not in specific categories:

SELECT name, category
FROM products
WHERE category NOT IN ('Electronics', 'Media')

`name`	`category`
`"Tool A"`	`"Hardware"`
`"Tool B"`	`"Hardware"`

Single Value IN¶

IN with a single value is equivalent to =:

-- These are equivalent:
WHERE category IN ('Hardware')
WHERE category = 'Hardware'

Empty List¶

IN with an empty list always returns FALSE:

SELECT * FROM products WHERE id IN ()
-- Returns no rows

Important Notes¶

NULL Handling¶

IN has special NULL semantics that can be surprising:

If the expression is NULL, IN returns NULL:

WHERE NULL IN (1, 2, 3)     -- Returns NULL

If the list contains NULL and no match is found, IN returns NULL (not FALSE):

WHERE 5 IN (1, 2, NULL)     -- Returns NULL (not FALSE)
WHERE 1 IN (1, 2, NULL)     -- Returns TRUE

NOT IN with NULL in the list can produce unexpected results:

-- Be careful with NOT IN when NULLs might be present
WHERE 5 NOT IN (1, 2, NULL) -- Returns NULL (not TRUE!)

To avoid NULL-related issues with NOT IN, consider filtering NULLs or using alternative approaches.

Equivalence¶

IN is shorthand for multiple comparisons:

-- These are equivalent:
WHERE x IN (1, 2, 3)
WHERE x = 1 OR x = 2 OR x = 3

-- These are equivalent:
WHERE x NOT IN (1, 2, 3)
WHERE x != 1 AND x != 2 AND x != 3

Type Compatibility¶

All values in the IN list must be of compatible types with the expression. Mixing incompatible types will result in a type error:

-- ERROR: Type mismatch
WHERE id IN (1, 'two', 3)

Performance Considerations¶

IN is most efficient with small, static value lists. For large value lists or dynamic values, consider:

Using a JOIN with a separate table
Creating an appropriate index
Using other filtering strategies

Subqueries¶

IN does not currently support subqueries:

-- NOT SUPPORTED:
WHERE category IN (SELECT category FROM popular_categories)

Use JOINs or other techniques for set-based filtering.

IN¶