IN Operator
In this page:
Introducing the IN Operator
The IN operator lets you specify multiple values in a WHERE clause. It is a shorthand way to write multiple OR conditions, so instead of chaining five equals-checks together you list all five values once. It makes your queries much easier to read and less error-prone to maintain.
Example: Introducing the IN Operator
SELECT * FROM users WHERE city IN ('Patna', 'Delhi', 'Mumbai');IN with Text Lists
You can use the IN operator with lists of strings, such as filtering orders by a set of status names. Make sure to wrap each string value in single quotes and separate them with commas. This is clean and direct compared to a long chain of OR conditions.
Example: IN with Text Lists
SELECT * FROM orders WHERE status IN ('pending', 'shipped');IN with Number Lists
Numbers do not need single quotes when used inside the IN list, unlike text values. You can simply list your numeric IDs or statuses inside the parentheses, e.g. WHERE id IN (3, 7, 12). This is fast and efficient for filtering on a known set of keys.
Example: IN with Number Lists
SELECT * FROM orders WHERE id IN (1, 2, 3);The NOT IN Operator
You can combine NOT with IN to exclude a specific list of values, such as skipping certain product categories. This returns all rows whose values do not match any items in your list. It helps you skip specific groups easily without writing several NOT-equals conditions.
Example: The NOT IN Operator
SELECT * FROM products WHERE category NOT IN ('discontinued', 'archived');Using IN with Subqueries
The real power of IN comes when you use a subquery instead of a hardcoded list. A subquery is a query inside another query, so its result set becomes the list IN checks against. This lets you filter dynamically based on data that changes, like 'customers who placed an order this month.'
Example: Using IN with Subqueries
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 100);Chapter Quiz — Complete all 8 topics to unlock
0/8 topics done
Complete these topics first: