IN Operator
Filters rows where a value matches any value in a provided list or subquery.
SELECT columns FROM table_name WHERE column IN (value1, value2, ...);This static page keeps the syntax and examples indexed for search, while the coding app handles interactive exploration and saved references.
What it does
Overview
Filters rows where a value matches any value in a provided list or subquery.
The IN operator evaluates whether a specified value matches any value in a list of literals or a result set returned by a subquery. It serves as a syntactic shorthand for multiple OR conditions (e.g., x IN (1, 2) is equivalent to x = 1 OR x = 2). Performance-wise, most modern SQL optimizers convert IN lists into a hash table or a sorted array to perform lookups in O(log n) or O(1) time, making it generally more efficient than a long chain of OR predicates. A critical edge case is SQL's three-valued logic involving NULLs: the expression 'value IN (1, NULL)' returns UNKNOWN if the value is not 1. This behavior is particularly dangerous with 'NOT IN'; if the target set contains a single NULL, the entire predicate will fail to return any rows because the comparison with NULL can never be proven TRUE. For massive datasets, joining against a temporary table is often preferred over a large static list to stay within system parameter limits and leverage index-nested loops.
Quick reference
Syntax
SELECT columns FROM table_name WHERE column IN (value1, value2, ...);Inputs
Parameters
See it in practice
Examples
Filtering with Static Literal Sets
SELECT first_name, last_name FROM employees WHERE department_id IN (10, 20, 30);List of employees in departments 10, 20, and 30.
Selects rows where the d matches any of the integers provided in the comma-separated list.
Dynamic Filtering using a Subquery
SELECT title FROM books WHERE author_id IN (SELECT id FROM authors WHERE nationality = 'Japanese');Titles of books written by Japanese authors.
The IN operator processes the inner query first and then filters the outer 'books' query based on the returned author IDs.
Negation with NOT IN
SELECT product_name FROM products WHERE category_id NOT IN (5, 12, 18);All products except those in categories 5, 12, and 18.
Returns all records where the d does not exist within the specified exclusion list.
Debug faster
Common Errors
LogicError
Cause: Including a NULL value in a NOT IN list or subquery, which results in zero rows being returned.
Fix: Ensure the subquery filters out NULLs using 'WHERE column IS NOT NULL' or use the NOT EXISTS operator instead.
SELECT * FROM users WHERE id NOT IN (SELECT supervisor_id FROM departments); -- Fails if any supervisor_id is NULLLimitError
Cause: Exceeding the maximum number of expressions allowed in an IN list (e.g., Oracle limits this to 1000 items).
Fix: Load the values into a temporary table and use a subquery or an INNER JOIN.
SELECT * FROM orders WHERE order_id IN (1, 2, ... 1001);Runtime support
Compatibility
Source: MDN Web Docs
Common questions
Frequently Asked Questions
Filters rows where a value matches any value in a provided list or subquery.
values/subquery: A list of values or a subquery returning one column.
LogicError: Ensure the subquery filters out NULLs using 'WHERE column IS NOT NULL' or use the NOT EXISTS operator instead. LimitError: Load the values into a temporary table and use a subquery or an INNER JOIN.