SQL WHERE IN: Filter by a List of Values

Employees in three departments:

SELECT first_name, last_name, department
FROM employees
WHERE department IN ('Design', 'Legal', 'Product')
ORDER BY department, last_name
LIMIT 8;

▶ Run this query in the SQL Playground

WHERE column IN (value_1, value_2, ...)
WHERE column NOT IN (value_1, value_2, ...)
WHERE column IN (SELECT column FROM ...)

Values are separated by commas inside round brackets. Text values need single quotes; numbers do not.

These two filters return the same rows:

SELECT (SELECT COUNT(*) FROM employees
        WHERE department IN ('Design', 'Legal', 'Product')) AS with_in,
       (SELECT COUNT(*) FROM employees
        WHERE department = 'Design' OR department = 'Legal' OR department = 'Product') AS with_or;

▶ Run this query in the SQL Playground

Use IN whenever you would otherwise repeat column = ... OR column = .... Adding a value is then just adding a comma. For different columns, such as department = 'Sales' OR salary > 190000, you still need OR.

Performance is the same: databases treat both forms alike. For very long lists, thousands of values, a join to a table of those values or a subquery is usually easier to maintain and can be faster.

Numbers

SELECT id, user_id, status, total
FROM orders
WHERE user_id IN (4, 77, 118)
ORDER BY user_id, id;

▶ Run this query in the SQL Playground

Text

SELECT category, COUNT(*) AS products
FROM products
WHERE category IN ('Books', 'Electronics', 'Toys')
GROUP BY category
ORDER BY category;

▶ Run this query in the SQL Playground

Text must match exactly, including spaces. Whether case matters depends on the database: SQLite and PostgreSQL compare with case ('books' does not match 'Books'), MySQL and SQL Server usually do not.

NOT IN keeps the rows whose value is in none of the list:

SELECT status, COUNT(*) AS orders
FROM orders
WHERE status NOT IN ('cancelled', 'refunded')
GROUP BY status
ORDER BY orders DESC;

▶ Run this query in the SQL Playground

Two NULL rules matter. First, rows where the column itself is NULL match neither IN nor NOT IN. Second, if the list contains a NULL, NOT IN matches nothing at all. That usually happens with a subquery:

SELECT
  (SELECT COUNT(*) FROM employees
   WHERE id NOT IN (SELECT manager_id FROM employees)) AS not_in_result,
  (SELECT COUNT(*) FROM employees
   WHERE id NOT IN (SELECT manager_id FROM employees
                    WHERE manager_id IS NOT NULL)) AS not_in_without_nulls;

▶ Run this query in the SQL Playground

manager_id is NULL for the employees at the top, so the first version finds 0 non-managers instead of 110. Filter NULLs out of the list, or use NOT EXISTS, which does not have this problem.

Instead of typing the list, a subquery can produce it. Orders from premium customers:

SELECT COUNT(*) AS premium_orders
FROM orders
WHERE user_id IN (SELECT id FROM users WHERE is_premium = 1);

▶ Run this query in the SQL Playground

The subquery must return exactly one column. For the full comparison of IN and EXISTS with subqueries, see IN vs EXISTS.

IN is just one condition, so it combines with AND and OR like any other. Sales and Marketing employees earning more than 150,000:

SELECT first_name, last_name, department, salary
FROM employees
WHERE department IN ('Sales', 'Marketing')
  AND salary > 150000
ORDER BY salary DESC;

▶ Run this query in the SQL Playground

Five errors and how to avoid them.

SELECT * FROM orders
WHERE user_id = (4, 77, 118);

SELECT * FROM orders
WHERE user_id IN (4, 77, 118);

WHERE category IN ['Books', 'Toys']

WHERE category IN ('Books', 'Toys')

WHERE category IN (Books, Toys)

WHERE category IN ('Books', 'Toys')

WHERE id NOT IN (SELECT manager_id FROM employees)

WHERE id NOT IN (SELECT manager_id FROM employees
                 WHERE manager_id IS NOT NULL)

WHERE shipped_date IN (NULL, '2024-12-16')

WHERE shipped_date IS NULL
   OR shipped_date = '2024-12-16'

5 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy List the users who live in Austin, Dallas or Houston. Show the first 8 by last name. Table: users

   Show solution

   SQLite · runs in the Playground

   SELECT first_name, last_name, city
   FROM users
   WHERE city IN ('Austin', 'Dallas', 'Houston')
   ORDER BY last_name, first_name
   LIMIT 8;

   ▶ Run this query in the SQL Playground

   One IN instead of three OR conditions.

2. 2Easy Count the payments made with a card (Credit Card or Debit Card). Table: payments

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(*) AS card_payments
   FROM payments
   WHERE method IN ('Credit Card', 'Debit Card');

   ▶ Run this query in the SQL Playground

   Text values in quotes, matching exactly.

3. 3Medium Count the employees in every department except HR and Finance. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT department, COUNT(*) AS employees
   FROM employees
   WHERE department NOT IN ('HR', 'Finance')
   GROUP BY department
   ORDER BY employees DESC, department;

   ▶ Run this query in the SQL Playground

   NOT IN is safe here: the list is literal and contains no NULL.

4. 4Medium List the products that appear on order 1, 2 or 3, using IN on order_items. Table: products, order_items

   Show solution

   SQLite · runs in the Playground

   SELECT DISTINCT p.name
   FROM products p
   WHERE p.id IN (SELECT product_id
                  FROM order_items
                  WHERE order_id IN (1, 2, 3))
   ORDER BY p.name;

   ▶ Run this query in the SQL Playground

   An IN with a literal list inside an IN with a subquery.

5. 5Medium Count the reviews with a rating of 1, 2 or 5. Table: reviews

   Show solution

   SQLite · runs in the Playground

   SELECT rating, COUNT(*) AS reviews
   FROM reviews
   WHERE rating IN (1, 2, 5)
   GROUP BY rating
   ORDER BY rating;

   ▶ Run this query in the SQL Playground

   A list of numbers; BETWEEN would not work because 3 and 4 must be left out.