SQL - IN Operator

SQL IN Operator: Filtering with a List of Values

The SQL IN operator is a powerful and readable way to filter data by checking if a column’s value matches any value in a specified list. It serves as a concise alternative to chaining multiple OR conditions, making your queries cleaner and easier to understand.

Basic Usage with a Static List

The primary use of the IN operator is to provide a list of literal values in the WHERE clause.

Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

Example

Consider a products table:

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10, 2)
);

INSERT INTO products (id, name, category, price) VALUES
(1, 'Laptop', 'Electronics', 1200.00),
(2, 'T-Shirt', 'Apparel', 25.00),
(3, 'Coffee Maker', 'Home Goods', 80.00),
(4, 'Jeans', 'Apparel', 75.00),
(5, 'Smartphone', 'Electronics', 950.00);

To find all products in the ‘Apparel’ or ‘Home Goods’ categories, you can use IN:

SELECT id, name, category FROM products
WHERE category IN ('Apparel', 'Home Goods');

Output

id	name	category
2	T-Shirt	Apparel
3	Coffee Maker	Home Goods
4	Jeans	Apparel

For comparison, the same query using OR is more verbose:

WHERE category = 'Apparel' OR category = 'Home Goods'

As the list grows, the benefit of IN becomes even more apparent.

Using NOT IN to Exclude Values

You can use NOT IN to retrieve all rows that do not match any value in the list. For example, to find all products that are not ‘Electronics’:

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

Common Pitfall: NOT IN with NULL

A major trap for developers is using NOT IN with a list that contains a NULL value. A condition like WHERE column NOT IN (value1, NULL) will never return any rows (unless the column itself is also NULL and your database settings treat NULL comparisons in a specific way). This is because col <> NULL evaluates to UNKNOWN, not TRUE or FALSE. The query effectively becomes un-satisfiable.

-- This query will return an empty result set due to the NULL
SELECT * FROM products WHERE category NOT IN ('Electronics', NULL);

-- The fix: ensure your list does not contain NULLs, or use an alternative like NOT EXISTS.
SELECT * FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM (VALUES ('Electronics'), (NULL)) AS excluded(cat)
  WHERE p.category = excluded.cat
);

Using IN with a Subquery

A more dynamic and powerful use of IN is to provide the list of values from another query (a subquery). The subquery must return a single column.

Example

Imagine you have a special_offers table and want to find all products currently on offer.

CREATE TABLE special_offers (product_id INT, campaign VARCHAR(50));
INSERT INTO special_offers VALUES (1, 'Winter Sale'), (4, 'Denim Deals');

-- Find full product details for products on offer
SELECT * FROM products
WHERE id IN (SELECT product_id FROM special_offers);

Output

id	name	category	price
1	Laptop	Electronics	1200.00
4	Jeans	Apparel	75.00

Performance Considerations: IN vs. JOIN vs. EXISTS

For the subquery example above, the same result can often be achieved with a JOIN. Modern database optimizers are very good, but historically and in certain complex scenarios, the choice can impact performance.

-- Using an INNER JOIN, often more performant
SELECT p.*
FROM products p
INNER JOIN special_offers so ON p.id = so.product_id;

• IN is very readable, but can be less performant with very large subquery lists, as the database might load the entire list into memory first.
• JOIN is generally preferred for this type of query. It allows the database optimizer to choose the most efficient join strategy (e.g., hash join, nested loop). It’s also more flexible if you need to select columns from the joined table.
• EXISTS is another powerful alternative, often excelling when you just need to check for the existence of a matching row and don’t care about the values themselves. It can be faster than IN because it can stop searching as soon as it finds a single match.

Pro Tip: For simple checks, choose the most readable option (IN or JOIN). For performance-critical queries, use your database’s EXPLAIN or ANALYZE tool to compare the query plans of IN, JOIN, and EXISTS to see which is most efficient for your specific data and schema.