The MySQL IN operator is used to filter data based on a specified set of values. It is a shorthand for multiple OR conditions which allows us to specify a list of values in a WHERE clause to match records that have any of those values. This makes your SQL queries more concise and easier to read.

MySQL IN Operator

The IN operator is a powerful and efficient way to filter data within a specified set of values. It is particularly useful when we need to match a column’s value against multiple possible values. Instead of writing multiple OR conditions, we can use the IN operator to check multiple values. It can be used with any data type.

Syntax:

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

Parameters:

• column1, column2, …: The columns to retrieve data from.
• table_name: The name of the table from which to retrieve data.
• column_name: The column to compare against the list of values.
• value1, value2, …: The list of values to check against.

Demo MySQL Database

We will create a sample database to see how to use the IN operator.

Create ‘studentsInfo‘ table:

CREATE TABLE studentsInfo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    grade CHAR(2),
    city VARCHAR(50)
);

INSERT INTO studentsInfo (name, age, grade, city) VALUES
('Amit Sharma', 18, 'A', 'Delhi'),
('Priya Singh', 19, 'B', 'Mumbai'),
('Raj Patel', 20, 'A', 'Ahmedabad'),
('Sneha Reddy', 21, 'C', 'Hyderabad'),
('Arjun Rao', 22, 'B', 'Bangalore');

Output

SELECT id, name, grade, city
FROM studentsInfo
WHERE grade IN ('A', 'B');

SELECT id, name, city
FROM studentsInfo
WHERE city NOT IN ('Mumbai', 'Bangalore');

SELECT id, name, age, city
FROM studentsInfo
WHERE age IN (18, 20, 22);