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.

This guide is for beginners and analysts who filter data every day. You will get the syntax, a table explaining each part, seven worked examples on the same two sample tables with the exact output, the NOT IN and NULL trap that catches most people, performance notes, and a short practice exercise. The examples run in MySQL, SQL Server, PostgreSQL and SQLite.

SQL IN Operator 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 Used in This Guide

Example 1: IN with a List of Text Values

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

Whether ‘india’ matches ‘India’ depends on the column collation: MySQL and SQL Server are usually case-insensitive, PostgreSQL and SQLite are case-sensitive.

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. The SQL subqueries guide explains how the inner query builds this list.

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';

When you mix IN with AND, IN keeps the logic grouped, so you avoid the bracket mistakes that long OR chains invite. See SQL AND, OR and NOT for operator precedence.

Example 6: IN with Dates

SELECT OrderID, OrderDate
FROM Orders
WHERE OrderDate IN ('2026-01-05', '2026-03-01');

IN matches exact dates only. If the column stores a time as well, or you want a continuous range, use BETWEEN or >= and < instead.

Example 7: IN Inside CASE, UPDATE and DELETE

IN works anywhere a condition is allowed, not only in WHERE. Group countries into regions:

SELECT Name,
       CASE WHEN Country IN ('UK', 'Spain') THEN 'Europe'
            WHEN Country IN ('India', 'Singapore') THEN 'Asia'
            ELSE 'Other' END AS Region
FROM Customers;

And change several rows at once:

UPDATE Orders SET Amount = Amount * 0.9
WHERE Product IN ('Mouse', 'Keyboard');   -- 102 and 104 get 10% off

Always run the same WHERE with SELECT first. More on safe changes in SQL UPDATE.

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. Plain IN is not affected: a NULL in the list simply never matches. The SQL NULL values chapter explains three-valued logic in detail.

IN vs OR vs EXISTS vs BETWEEN

Database Differences and Performance

IN, NOT IN and IN (subquery) are standard and behave the same in MySQL, SQL Server, PostgreSQL and SQLite. For a short list of literals the optimizer usually turns IN into a series of index lookups. 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 generated by application code. SQL Server can struggle with lists beyond a few thousand items, and Oracle caps a literal list at 1,000 values. Load long lists into a temporary table and join to it instead.

Common Errors and Fixes

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

Want more practice? Try the SQL quiz with 75 questions. If you filter lists in spreadsheets as well, the same idea appears in Excel formulas; the Excel AI Mastery: 100 Formulas course on NextGenTemplates Academy covers list-based filtering and lookups with FILTER and XLOOKUP.

Frequently Asked Questions

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 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.

Can I use IN with LIKE wildcards?

No. IN only does exact matches. For patterns, combine several LIKE conditions with OR.

How many values can an IN list hold?

There is no fixed standard limit, but Oracle allows 1,000 literals and very long lists slow down other engines. Use a temporary table and a join for thousands of values.

Should I use IN or EXISTS with a subquery?

For positive matches they usually perform the same. For exclusions, NOT EXISTS is the safer choice because it is not affected by NULLs.

Wrapping Up

The IN operator keeps your filters short and readable: list the values, or let a subquery build the list for you. Use NOT IN only when you are sure the list has no NULLs, and switch to NOT EXISTS when it comes from another table. Next, learn how to filter ranges with SQL BETWEEN.

📅 Last updated: October 2026

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