Skip to main content

IN Operator

The IN operator is used to filter rows based on whether a value matches any value in a specified list or subquery. It simplifies multiple equality checks and is often used in WHERE clauses.

Syntax

value [NOT] IN (value1, value2, ..., valueN)
value [NOT] IN (subquery)

  • value: The column or expression being tested.

  • value1, value2, ..., valueN: A list of literal values or expressions to compare against.

  • subquery: A subquery that returns a result set to compare against. ## Limits

  • The list can contain any number of elements, but excessively large lists may impact performance.

  • For subqueries, ensure the result set contains one column. A multi-column subquery will throw an error.

Returns

  • Returns TRUE if value matches any value in the list or result set from the subquery.

  • Returns FALSE if no match is found.

  • Returns NULL if value is NULL or if value does not match any item in the list, and the list or subquery contains NULL.

Examples

Using a list of values

-- Example 1: Check if a column matches any value in a list
> SELECT * 
  FROM employees 
  WHERE department_id IN (1, 2, 3);

-- Example 2: Using NOT IN to exclude specific values
> SELECT * 
  FROM products 
  WHERE category NOT IN ('Electronics', 'Furniture');

Using a subquery

-- Example 3: Compare a value against a subquery result
> SELECT * 
  FROM orders 
  WHERE customer_id IN (SELECT customer_id FROM vip_customers);

-- Example 4: Using NOT IN with a subquery
> SELECT * 
  FROM orders 
  WHERE product_id NOT IN (SELECT product_id FROM discontinued_products);