SQL In

The IN operator matches a value against a list of values. It is often cleaner than multiple OR conditions and can also be used with subqueries.

On this page

SQL IN Operator

The IN operator is used in a WHERE clause to match a column against a list of values. It is often cleaner and easier to read than multiple OR conditions.

Basic Syntax

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

Example: IN with Text Values

Return customers from selected countries:

SELECT name, country
FROM customers
WHERE country IN ('USA', 'Canada', 'UK');

Example: IN with Numeric Values

Return orders with specific ids:

SELECT id, order_date
FROM orders
WHERE id IN (1001, 1002, 1010);

IN vs OR

These two queries are equivalent, but IN is usually shorter and clearer:

-- Using OR
SELECT name, country
FROM customers
WHERE country = 'USA' OR country = 'Canada' OR country = 'UK';

-- Using IN
SELECT name, country
FROM customers
WHERE country IN ('USA', 'Canada', 'UK');

NOT IN

Use NOT IN to exclude a list of values.

SELECT name, country
FROM customers
WHERE country NOT IN ('USA', 'Canada');

IN with a Subquery

IN can also be used with a subquery. This is useful when the list of values comes from another table.

SELECT name
FROM customers
WHERE id IN (
  SELECT customer_id
  FROM orders
);

NULL Note (Especially for NOT IN)

Be careful with NOT IN if the compared column or subquery can produce NULL values. NULL can cause the logic to behave unexpectedly. If needed, filter NULL values explicitly or consider using NOT EXISTS (covered later).

Performance Note

IN with a short list is usually fine. For large lists, performance depends on indexes and query plans. When using subqueries, indexing the compared columns can make a big difference.

Next Step

Continue with SQL BETWEEN to learn how to filter values within a range.

← SQL Wildcards

SQL Between →