WHERE with IN list in SQL

Introduction

The WHERE with IN list helps you find rows where a column matches any value from a list. It makes checking many values easy and fast.

You want to find customers from a few specific cities.

You need to get orders with certain status codes.

You want to filter products that belong to several categories.

You want to check if an employee's department is one of a few departments.

You want to select students who are in a list of classes.

Syntax

SQL

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

The list inside IN is a set of values to match.

Values can be numbers or strings (strings need quotes).

Examples

Selects employees who work in department 1, 2, or 3.

SQL

SELECT * FROM employees WHERE department_id IN (1, 2, 3);

Finds products in either 'Books' or 'Toys' categories.

SQL

SELECT name FROM products WHERE category IN ('Books', 'Toys');

Checks orders with status exactly 'Pending'. This is like using = but with IN list.

SQL

SELECT * FROM orders WHERE status IN ('Pending');

This is an empty list. It returns no rows because no city matches nothing.

SQL

SELECT * FROM customers WHERE city IN ();

Sample Program

This creates a simple employees table, adds four employees, then selects those in departments 1, 3, or 5.

SQL

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  department_id INT
);

INSERT INTO employees (id, name, department_id) VALUES
(1, 'Alice', 1),
(2, 'Bob', 2),
(3, 'Charlie', 3),
(4, 'Diana', 4);

-- Find employees in departments 1, 3, or 5
SELECT id, name, department_id FROM employees WHERE department_id IN (1, 3, 5);

Important Notes

The IN list makes queries shorter and easier than many OR conditions.

Using a large list in IN can slow down the query; consider joins or temporary tables for big lists.

Remember that IN checks for exact matches; it does not work like LIKE or partial matches.

Summary

WHERE with IN list filters rows by matching a column to any value in a list.

It is easier and cleaner than writing many OR conditions.

Use it when you have a fixed set of values to check against.