SQL IN Operator: Syntax, Usage, and Examples

The IN operator lets you check if a value matches anything in a list of options. It’s a clean way to replace long chains of OR conditions in SQL queries.

How to Use the IN operator

You use IN inside a WHERE clause. The database checks if a value exists in a list you provide, or in a list returned by a subquery.

Basic syntax

SELECT columns
FROMtable
WHERE column_nameIN (value1, value2, value3);

If column_name matches any value in the parentheses, the row qualifies.

Using IN with strings

SELECT id, name, country
FROM customers
WHERE countryIN ('Austria','Germany','Switzerland');

This returns customers from those three countries.

Using IN with numbers

SELECT order_id, customer_id, total
FROM orders
WHERE customer_idIN (101,205,309);

This returns orders placed by those customers.

Using IN with a subquery

Instead of writing the values yourself, you can pull them from another query.

SELECT name, email
FROM users
WHERE idIN (
SELECT user_id
FROM newsletter_subscribers
);

This returns all users who appear in newsletter_subscribers.

When to Use the IN operator

The IN operator is most helpful when you need to filter rows using a set of “allowed values” and you want your query to stay readable.

1) Filtering by multiple possible values

This is the classic use case. Your table stores one value per row, and you want several of them.

Examples:

• Customers from a group of countries
• Orders with specific status values
• Products from a list of categories

2) Replacing repetitive OR logic

You could write this:

WHERE status='pending'
OR status='processing'
OR status='shipped'

Or you could write the same thing in one line:

WHERE statusIN ('pending','processing','shipped')

Your future self will thank you.

3) Filtering using results from another table

IN becomes even more useful when your list comes from a subquery.

Examples:

• Users who have purchased something
• Products that have at least one review
• Students enrolled in a course

4) Quick “segment” style queries

In analytics, IN is great for creating a segment without building a whole join.

Examples:

• “Show metrics for these 5 campaigns”
• “Only include these regions”
• “Only track specific event types”

Examples of the IN operator

Let’s walk through common patterns you’ll use in real projects.

Example 1: Filtering by multiple categories

Imagine an online shop with a products table.

SELECT product_id, name, category
FROM products
WHERE categoryIN ('laptops','monitors','keyboards');

This returns products in those categories only.

Example 2: Filtering orders by status

Many apps store order status as text.

SELECT order_id, status, total
FROM orders
WHERE statusIN ('paid','shipped','delivered');

This filters down to active or completed orders.

Example 3: Filtering by a list of IDs

Sometimes you just have a list and you need results fast. For example, a support teammate sends a few customer IDs to check.

SELECT id, name, email
FROM customers
WHERE idIN (12,47,86,104);

Example 4: Using IN with a subquery for “has done X”

Example: find customers who have placed at least one order.

SELECT id, name
FROM customers
WHERE idIN (
SELECT customer_id
FROM orders
);

If a customer appears in orders, they qualify.

Example 5: Combining IN with other conditions

You can stack it with other filters like AND.

SELECT id, name, country, is_active
FROM customers
WHERE countryIN ('Germany','France','Spain')
AND is_active=TRUE;

This returns active users from those countries only.

Example 6: Using NOT IN to exclude values

NOT IN does the opposite.

SELECT id, name, status
FROM orders
WHERE statusNOTIN ('cancelled','refunded');

This removes unwanted statuses from the result.

One small warning: NOT IN behaves differently with NULL values, and we’ll talk about that in the next section.

Learn More About the IN operator

IN vs OR

Both do the same job in many cases, but IN is easier to read and maintain.

This:

WHERE country='Italy'
OR country='Greece'
OR country='Portugal'

Becomes:

WHERE countryIN ('Italy','Greece','Portugal')

If you ever need to add another value, you won’t have to copy-paste another OR.

IN vs EXISTS

IN and EXISTS can look similar because both can use subqueries, but they work differently.

Example using IN:

SELECT name
FROM users
WHERE idIN (
SELECT user_id
FROM purchases
);

Example using EXISTS:

SELECT name
FROM users u
WHEREEXISTS (
SELECT1
FROM purchases p
WHERE p.user_id= u.id
);

EXISTS checks row-by-row and stops as soon as it finds a match. Many databases optimize both well, but EXISTS often shines when the subquery is large or when you need a correlated condition.

A simple rule:

• Use IN when you have a clear list of values or a simple subquery
• Use EXISTS when the logic depends on the outer query row

How IN handles NULL values

This trips people up, especially with NOT IN.

Example:

SELECT id
FROM users
WHERE countryNOTIN ('Austria','Germany');

If country can be NULL, rows with NULL do not pass this check. In SQL’s three-valued logic, NULL means “unknown,” so the database can’t confidently say the value is “not in the list.”

If you want to include NULL rows too, you need to handle them explicitly:

SELECT id
FROM users
WHERE countryNOTIN ('Austria','Germany')
OR countryISNULL;

IN with dates

You can use IN with dates, but you need to format them correctly for your database.

SELECT id, created_at
FROM orders
WHERE created_atIN ('2026-01-01','2026-01-15');

This is useful for quick spot-checking specific days.

If you need ranges, BETWEEN usually fits better.

IN with a dynamic list from another table

Sometimes the list of values changes over time, so hardcoding them isn’t a good idea.

Example: filter products based on “featured categories” stored in a table.

SELECT product_id, name, category
FROM products
WHERE categoryIN (
SELECT category_name
FROM featured_categories
);

Now marketing can update the categories without you editing the query.

IN with a long list

A huge IN (...) list can get messy, and performance can vary depending on the database.

If your list gets too long, you have cleaner options:

• Put the values into a temporary table and join
• Use a real lookup table
• Use a subquery instead of manually typing values

Example using a join:

SELECT p.product_id, p.name
FROM products p
JOIN featured_products fON f.product_id= p.product_id;

This scales better and stays readable.

Common mistakes with IN

1) Forgetting parentheses

Wrong:

WHERE statusIN'paid','shipped'

Right:

WHERE statusIN ('paid','shipped')

2) Mixing numbers and strings

If the column is numeric, don’t quote the values:

WHERE customer_idIN (10,20,30)

3) Using NOT IN without thinking about NULL

If the column can contain NULL, you might filter out more rows than you expected.

Summary

The IN operator checks if a value matches any value in a list, or any value returned by a subquery. Use it to simplify filters, replace long OR conditions, and build clean queries that are easy to modify later.

When you use NOT IN, remember that NULL can affect the results, so add IS NULL checks when needed.