SQL IN Operator: Filter by a List of Values (Examples)

SQL Tutorial · Chapter 11 of 32

The SQL IN operator tests whether a column value matches any value in a list. It is a compact replacement for a chain of OR conditions: City IN ('Mumbai', 'Sydney') means the same as City = 'Mumbai' OR City = 'Sydney'. The list can be typed out or produced by a subquery, and NOT IN excludes every value in the list.

SQL IN Syntax

SELECT columns FROM table_name
WHERE column IN (value1, value2, ...);

SELECT columns FROM table_name
WHERE column NOT IN (value1, value2, ...);

SELECT columns FROM table_name
WHERE column IN (SELECT column FROM other_table);

Sample Tables

Example 1: IN with a List of Text Values

SELECT Name, Country
FROM Customers
WHERE Country IN ('India', 'Spain', 'Australia');

Example 2: IN with Numbers

SELECT OrderID, CustomerID, Amount
FROM Orders
WHERE CustomerID IN (1, 4);

Example 3: NOT IN

SELECT OrderID, Product
FROM Orders
WHERE Product NOT IN ('Laptop', 'Monitor');

Example 4: IN with a Subquery

The list can come from another query. Here we find customers who have placed at least one order.

SELECT Name
FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders);

Emma Brown (customer 5) has no orders, so she is left out. Reverse it with NOT IN to find customers who have never ordered: only Emma Brown.

Example 5: IN vs OR

These two queries return the same rows. IN is shorter, easier to read and easier for the optimizer to turn into an index lookup.

SELECT Name FROM Customers WHERE City IN ('London', 'Sydney');
SELECT Name FROM Customers WHERE City = 'London' OR City = 'Sydney';

The NOT IN and NULL Trap

If the list (or subquery) contains a NULL, NOT IN returns no rows at all, because value <> NULL is unknown rather than true. Filter NULLs out of the subquery with WHERE column IS NOT NULL, or use NOT EXISTS, which handles NULLs correctly.

Works in MySQL, SQL Server, PostgreSQL, SQLite

IN, NOT IN and IN (subquery) are standard and behave the same in all four. Practical limits differ: SQL Server can struggle with lists beyond a few thousand items, Oracle caps literal lists at 1,000. For very long lists, load the values into a temporary table and join to it instead.

IN and Performance

For a short list of literals the optimizer usually turns IN into a series of index lookups, which is as fast as any filter can be. For IN with a subquery, most modern engines rewrite it as a semi-join, so it performs like EXISTS. Problems appear only with very long literal lists (thousands of values) generated by application code; these bloat the query text and can exceed parser limits. Insert those values into a temporary table and join to it instead.

Common Mistakes

• Forgetting the parentheses around the list.
• Mixing data types in the list, such as IN (1, '2'), which forces conversions.
• Using NOT IN against a subquery that can return NULL and getting an empty result.
• Writing a subquery that returns more than one column; IN needs exactly one.

Practice Exercise

1. Return orders whose Product is Mouse, Keyboard or Headset.
2. Return the names of customers who have never placed an order.

-- 1
SELECT OrderID, Product FROM Orders
WHERE Product IN ('Mouse', 'Keyboard', 'Headset');
-- 102, 104, 105

-- 2
SELECT Name FROM Customers
WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders WHERE CustomerID IS NOT NULL);
-- Emma Brown

Related Chapters

• SQL AND, OR, NOT – the OR chains IN replaces
• SQL Subqueries – building lists with a query
• SQL EXISTS – the NULL-safe alternative to NOT IN
• SQL from Scratch: full course overview

FAQ

What does IN do in SQL?

IN checks whether a value matches any item in a list. It returns TRUE if there is a match, so the row is included, and is equivalent to several equality tests joined by OR.

Is IN faster than OR?

Usually the optimizer treats them the same, but IN is easier to read and some databases convert IN lists into efficient index lookups more reliably than long OR chains.

Why does NOT IN return nothing?

Because the list contains a NULL. Any comparison with NULL is unknown, so no row can pass. Remove NULLs from the list or use NOT EXISTS.

Chapter 11 of 32 · SQL from Scratch: all 32 chapters