The SQL IN Operator: Efficient Filtering of Multiple Values

When you need rows where a column matches any value in a list — customers in New York or London, orders that are shipped or delivered, products in three specific categories — the SQL IN operator is the clean way to say it. It replaces a long chain of OR conditions with a single, readable list, and every major database speaks it the same way.

This guide is different from most: you can run every example right here. The playground below is a real SQL engine written in JavaScript — it runs entirely in your browser against a small sample database, nothing is sent anywhere. Edit the query, press Run, and watch the rows change. That feedback loop is how filtering actually clicks.

Quick answer: what does IN do?

IN tests whether a value matches any item in a list. It lives in the WHERE clause and returns every row whose column equals one of the listed values:

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

That single line means exactly the same thing as column_name = value1 OR column_name = value2 OR column_name = value3 — just shorter, easier to read, and easier to change later.

Basic syntax of the IN operator

IN is built from three fundamentals — the SELECT list, the FROM table, and the WHERE filter. To retrieve every customer who lives in New York or London:

SELECT *
FROM customers
WHERE city IN ('New York', 'London');

The list goes inside parentheses, values are separated by commas, and text values are wrapped in single quotes. You can list as many values as you need. Try adding 'Chicago' to the list in the playground above and running it again.

IN vs. a stack of ORs

The IN operator is really shorthand for a pile of OR comparisons. These two queries return identical results:

-- Verbose: one OR per value
SELECT product, category, amount
FROM orders
WHERE category = 'Electronics' OR category = 'Home';

-- Concise: one IN list
SELECT product, category, amount
FROM orders
WHERE category IN ('Electronics', 'Home');

Run them both and compare. As the number of values grows, IN wins on readability every time — and it makes intent obvious to the next person who reads your query. (For the full story on chaining conditions, see logical operators in SQL: AND, OR, and NOT.)

Filtering multiple values in one column

A common question is how to filter one column by several values at once — say, pulling products from three categories in a single pass. That is exactly the job IN was designed for:

SELECT *
FROM products
WHERE category IN ('Electronics', 'Clothing', 'Home');

One column, one list, one scan of the table. There is no need to run three separate queries and stitch the results together.

Numbers, strings, and dates in the IN list

The values in your list must be the right type for the column:

-- Text column: quote every value
WHERE city IN ('New York', 'London')

-- Numeric column: no quotes
WHERE customer_id IN (1, 3, 6, 10)

-- Dates: quote them like text ('YYYY-MM-DD' sorts chronologically)
WHERE order_date IN ('2023-05-02', '2023-06-11')

Mixing types is a common source of surprises. Quoting a number against a numeric column (WHERE id IN ('1')) is usually harmless — the database converts the literal once, and the index still works. The mistake that actually hurts is the mirror image: comparing a number against a text column (WHERE zip IN (90210, 60614) where zip is VARCHAR) forces a conversion of the column on every row, which defeats its index and turns a fast lookup into a full scan. Match the column’s type and you sidestep the whole class of problems.

NOT IN — and the NULL trap that returns zero rows

NOT IN flips the test: keep rows whose value is not in the list.

SELECT id, product, status
FROM orders
WHERE status NOT IN ('cancelled', 'pending');

Here is the footgun that catches everyone eventually: if the list contains a NULL, NOT IN returns no rows at all. Not an error — just an empty, silent result set. Run the first example below, then load “Add NULL to the list” and watch every row vanish:

Why does it happen? SQL uses three-valued logic: comparisons against NULL return UNKNOWN, not true or false. status NOT IN ('cancelled', NULL) expands to status <> 'cancelled' AND status <> NULL, and status <> NULL is always UNKNOWN, so no row can ever be strictly true. The fixes:

• Strip NULLs from the list (or the subquery feeding it) before using NOT IN.
• Add OR column IS NULL when you actually want the NULL rows back.
• Prefer NOT EXISTS, which handles NULL sanely — more on that below.

IN with a subquery: dynamic lists

The list does not have to be hard-coded. Feed IN a subquery and the values are computed at run time from another table:

SELECT product, amount, order_date
FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE country = 'USA'
);

The inner query returns the IDs of every US customer; the outer query returns their orders. When the customer list changes, the query keeps working — nothing to edit. The only rule: the subquery must return exactly one column, because IN compares against a single value.

Combining IN with AND, OR, and other filters

IN is just one condition — combine it with anything else in the WHERE clause:

SELECT *
FROM products
WHERE category IN ('Electronics', 'Clothing')
  AND price > 50;

This returns products that are in one of those two categories and cost more than 50. Mind your parentheses when you mix AND and OR — AND binds tighter than OR, so wrap the OR group when you need it evaluated first: WHERE (a OR b) AND c. See BETWEEN for filtering numeric and date ranges alongside your IN lists.

The IN operator across databases

IN is part of the SQL standard, so the core syntax is identical in MySQL, PostgreSQL, SQL Server, Oracle, SQLite, Snowflake, and BigQuery. A few dialect notes worth knowing:

• Oracle historically capped a hard-coded IN list at 1,000 items (ORA-01795); Oracle 23ai raised the ceiling to 65,535. Past the limit, use a subquery, a temporary table, or a join.
• SQL Server has no fixed IN-list limit but a practical ceiling around 2,100 parameters per statement — the same reason huge parameterized lists fail.
• PostgreSQL also accepts column = ANY(ARRAY['a','b']), which is handy when you want to bind a single array parameter from application code instead of expanding placeholders.
• MySQL offers FIND_IN_SET(value, 'a,b,c') for the special case where the list itself lives in one comma-separated column — a design to avoid when you can, but useful for legacy schemas.

Passing an IN list safely from application code

If you build IN lists from user input, never concatenate values into the string — that is textbook SQL injection. Most drivers cannot bind a whole list to a single ?, so generate one placeholder per value and bind them individually:

-- App builds this for a 3-item list, then binds 3 parameters
SELECT * FROM orders WHERE customer_id IN (?, ?, ?);

Whether you are wiring this into a Python service, a Node API, or a visual pipeline, the principle is the same — parameterize the values. (On when to reach for SQL versus code, see Python vs. SQL in data engineering.)

LIKE with multiple values (what IN can’t do)

IN matches exact values only — it has no wildcards. If you need partial matches against several patterns (“names containing ‘berg’ or ending in ‘son’”), IN is the wrong tool. Reach for the LIKE operator, one pattern per condition:

-- Standard SQL: one LIKE per pattern
SELECT * FROM products
WHERE name LIKE '%Mouse' OR name LIKE '%Keyboard';

Some databases add shortcuts, but the syntax differs: PostgreSQL takes an array — LIKE ANY (ARRAY['%a%', '%b%']) — while Snowflake takes a plain pattern list — LIKE ANY ('%a%', '%b%') (with ILIKE ANY for case-insensitive). MySQL can fold the patterns into one regex, REGEXP 'a|b'. SQL Server has none of these — you write out the OR LIKE chain or use a pattern table. Try the exact-match limitation for yourself:

Performance: IN vs. OR vs. EXISTS vs. JOIN

For a list of literal values, IN and the equivalent OR chain usually compile to the same plan — pick IN for readability. Where it gets interesting is subqueries:

• IN (SELECT ...) is fine for small, distinct result sets. Most optimizers rewrite it into a semi-join.
• EXISTS (SELECT ...) is often faster for large or correlated checks, and it does not fall into the NOT IN / NULL trap — prefer NOT EXISTS over NOT IN whenever the subquery column can be nullable.
• JOIN wins when you also need columns from the other table, not just a membership test.
• Make sure the filtered column is indexed. An IN list on an indexed column is a fast series of index seeks; on an unindexed column it is a full scan no matter how you write it.

As always, the real answer is to look at your query plan on your data. The shape of the tables and the indexes decide the winner.

Practice: three challenges

Reading is one thing; writing the query is another. Each exercise below checks your result against the expected answer and offers hints if you get stuck. No pressure — experiment freely, it all runs in your browser.

Frequently asked questions

What SQL operator filters for multiple values in one column? The IN operator. WHERE column IN (value1, value2, ...) returns rows matching any value in the list — the concise replacement for a chain of OR conditions.

How do I write a WHERE clause with multiple values? Use IN: WHERE city IN ('New York', 'London', 'Chicago'). Quote text values, leave numbers unquoted, and separate items with commas.

Can I use IN with a subquery? Yes. WHERE id IN (SELECT id FROM other_table WHERE ...) builds the list dynamically. The subquery must return exactly one column.

Why does my NOT IN query return no rows? Almost always a NULL in the list or subquery. NOT IN with any NULL yields an empty result under SQL’s three-valued logic. Filter the NULLs out, or use NOT EXISTS.

Is IN the same in MySQL, PostgreSQL, SQL Server, and Oracle? The core syntax is identical everywhere. The differences are edge cases: Oracle’s historical 1,000-item list cap (raised in 23ai), PostgreSQL’s = ANY(ARRAY[...]), and MySQL’s FIND_IN_SET.

From filtering a query to building a pipeline

Everything you just did by hand — matching a column against a list, feeding one query’s output into another’s filter — is the daily work of moving and shaping data. Doing it once in a WHERE clause is easy. Doing it across a dozen sources, on a schedule, without hand-writing and babysitting SQL scripts, is where teams get stuck.

That is exactly what we built ET1 for. It is a visual, asynchronous ETL tool where the IN-style filter you just wrote is a Filter node you drop onto a canvas — pointed at a CSV, a PostgreSQL table, or a REST API — with a live preview of the rows coming through. The subquery pattern becomes a Joiner wiring one dataset’s output into another’s filter. Same logic you just practiced, no boilerplate to maintain.

If SQL filtering just clicked for you, see what it looks like as a pipeline →