SQL In

Why the IN operator exists

Sometimes you want rows that match one of several specific values. You could write this with a chain of OR conditions, but that quickly becomes long and hard to read. The IN operator solves the problem by letting you list all the acceptable values in one place, and a row qualifies if its column equals any value in that list.

IN is essentially shorthand for a series of equality tests joined by OR. The two queries below produce the same result, but the IN version is shorter and clearer, especially as the list grows.

-- Using IN
SELECT customer_name, country
FROM Customers
WHERE country IN ('Germany', 'France', 'Spain');

-- The equivalent OR version
SELECT customer_name, country
FROM Customers
WHERE country = 'Germany'
   OR country = 'France'
   OR country = 'Spain';

Syntax and value lists

The operator follows the column name and takes a comma-separated list of values wrapped in parentheses. Text values need single quotes, while numeric values do not. The list can contain as many values as you need.

SELECT product_name, category_id
FROM Products
WHERE category_id IN (1, 3, 5, 8);

Excluding values with NOT IN

Placing NOT before IN reverses the test. The query then returns rows whose value is not present in the list, which is a clean way to exclude a known set of options.

SELECT customer_name, country
FROM Customers
WHERE country NOT IN ('USA', 'Canada', 'Mexico');

Using a subquery instead of a fixed list

The list of values does not have to be typed by hand. You can supply a subquery that returns a single column, and IN will test each row against the values that query produces. This is powerful for filtering one table based on data in another.

SELECT customer_name
FROM Customers
WHERE customer_id IN (
    SELECT customer_id
    FROM Orders
    WHERE order_total > 1000
);

• IN keeps queries short and readable when checking a column against several values.
• Wrap text values in single quotes; leave numbers unquoted.
• NOT IN returns the rows whose value is absent from the list.
• The list can be a literal set of values or a subquery that returns one column.