MySQL IN Operator

The IN operator in MySQL checks whether a column value matches any value in a given list. It is a cleaner and shorter alternative to writing multiple OR conditions in a WHERE clause.

Why Use IN?

Without IN, filtering for multiple specific values requires chaining OR conditions:

SELECT * FROM employees WHERE city = 'Delhi' OR city = 'Mumbai' OR city = 'Chennai';

Using IN, the same result is achieved more clearly:

SELECT * FROM employees WHERE city IN ('Delhi', 'Mumbai', 'Chennai');

Syntax

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

Sample Table

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    department VARCHAR(50),
    city VARCHAR(50),
    salary DECIMAL(10,2)
);

INSERT INTO employees VALUES (1, 'Ravi Kumar', 'HR', 'Delhi', 45000);
INSERT INTO employees VALUES (2, 'Sneha Joshi', 'IT', 'Mumbai', 72000);
INSERT INTO employees VALUES (3, 'Tarun Das', 'Finance', 'Kolkata', 55000);
INSERT INTO employees VALUES (4, 'Pooja Nair', 'IT', 'Chennai', 68000);
INSERT INTO employees VALUES (5, 'Vikram Singh', 'HR', 'Jaipur', 40000);

Example: Filter by Department

SELECT emp_name, department
FROM employees
WHERE department IN ('HR', 'IT');

Result:

emp_name     | department
-------------+-----------
Ravi Kumar   | HR
Sneha Joshi  | IT
Pooja Nair   | IT
Vikram Singh | HR

Example: Filter by City

SELECT emp_name, city
FROM employees
WHERE city IN ('Delhi', 'Mumbai', 'Chennai');

NOT IN

NOT IN returns rows where the column value is not in the list.

SELECT emp_name, department
FROM employees
WHERE department NOT IN ('HR', 'Finance');

Result: Returns only IT employees.

IN with Numbers

SELECT emp_name, salary
FROM employees
WHERE emp_id IN (1, 3, 5);

Returns employees with IDs 1, 3, and 5.

IN with a Subquery

IN is also commonly used with subqueries to filter based on results from another query.

SELECT emp_name
FROM employees
WHERE department IN (
    SELECT department FROM departments WHERE location = 'Delhi'
);

This returns employees in departments located in Delhi — even without knowing the department names in advance.

IN vs OR

NULL and IN

If a list in IN contains NULL, it does not match any row. To check for NULL values, use IS NULL separately.

Key Points

• IN checks if a column value matches any value in a list.
• It replaces multiple OR conditions with a cleaner syntax.
• NOT IN returns rows where the value is not in the list.
• IN works with numbers, strings, and dates.
• IN can accept a subquery as its value list.