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
FROM table
WHERE column_name IN (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 country IN ('Austria', 'Germany', 'Switzerland');

This returns customers from those three countries.

Using IN with numbers

SELECT order_id, customer_id, total
FROM orders
WHERE customer_id IN (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 id IN (
  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 status IN ('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 category IN ('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 status IN ('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 id IN (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 id IN (
  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 country IN ('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 status NOT IN ('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 country IN ('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 id IN (
  SELECT user_id
  FROM purchases
);

Example using EXISTS:

SELECT name
FROM users u
WHERE EXISTS (
  SELECT 1
  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 country NOT IN ('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 country NOT IN ('Austria', 'Germany')
  OR country IS NULL;

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_at IN ('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 category IN (
  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 f ON f.product_id = p.product_id;

This scales better and stays readable.

Common mistakes with IN

1) Forgetting parentheses

Wrong:

WHERE status IN 'paid', 'shipped'

Right:

WHERE status IN ('paid', 'shipped')

2) Mixing numbers and strings

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

WHERE customer_id IN (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.