MySQL IN Operator

The IN operator allows you to check whether a column's value matches any value in a given list. It is a concise alternative to writing multiple OR conditions and makes queries much easier to read, especially when the list is long.

Syntax

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

IN with Text Values

Retrieve students from a specific set of cities:

SELECT * FROM students
WHERE city IN ('Dhaka', 'Sylhet', 'Rajshahi');

id	name	city	age
1	Rahim Uddin	Dhaka	22
3	Karim Ali	Sylhet	23
4	Nila Akter	Dhaka	21
5	Rafi Hossain	Rajshahi	24

IN with Numbers

Retrieve specific rows by their primary key IDs — a very common pattern:

SELECT * FROM students
WHERE id IN (1, 3, 5);

id	name	city	age
1	Rahim Uddin	Dhaka	22
3	Karim Ali	Sylhet	23
5	Rafi Hossain	Rajshahi	24

IN vs Multiple OR Conditions

These two queries return identical results. The IN version is shorter and easier to read:

-- Using OR (verbose)
SELECT * FROM students
WHERE city = 'Dhaka' OR city = 'Sylhet' OR city = 'Rajshahi';

-- Using IN (clean)
SELECT * FROM students
WHERE city IN ('Dhaka', 'Sylhet', 'Rajshahi');

Tip: Use IN whenever you are comparing one column against three or more values. It is easier to write, easier to read, and easier to maintain.

NOT IN

NOT IN returns rows where the column value does not appear in the list:

-- Students NOT from Dhaka or Chittagong
SELECT * FROM students
WHERE city NOT IN ('Dhaka', 'Chittagong');

id	name	city	age
3	Karim Ali	Sylhet	23
5	Rafi Hossain	Rajshahi	24

NOT IN and NULL Values

Be careful when using NOT IN with a list that might contain NULL. If any value in the list is NULL, NOT IN returns no rows at all — because comparing anything to NULL is unknown in SQL.

-- If the list contains NULL, NOT IN returns nothing
SELECT * FROM students WHERE id NOT IN (1, NULL, 3);
-- Returns: 0 rows (unexpected!)

Note: Always make sure the values in your NOT IN list are free of NULL. If you are using a subquery inside NOT IN, ensure the subquery cannot return NULL.

IN with UPDATE and DELETE

IN works the same way in UPDATE and DELETE:

-- Update city for specific students
UPDATE students
SET city = 'Comilla'
WHERE id IN (2, 4);

-- Delete students not from major cities
DELETE FROM students
WHERE city NOT IN ('Dhaka', 'Chittagong', 'Sylhet');

Key Points to Remember

IN checks if a column value matches any item in a list — equivalent to multiple OR conditions.
NOT IN returns rows where the column does not match any value in the list.
Use IN over multiple OR conditions for clarity and maintainability.
NOT IN behaves unexpectedly when the list contains NULL — it returns zero rows.
IN works identically in SELECT, UPDATE, and DELETE statements.

What's next? The next lesson covers BETWEEN — a clean way to filter rows where a value falls within a defined range, without writing two separate conditions.

« Previous Lesson Next Lesson »