MySQL IN Operator

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

MySQL IN Operator Example

Let’s see some examples of IN Operator and understand it works in MySQL:

Example 1: Select students who are in grades 'A' or 'B'

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

Output:

Explanation: This query retrieves students whose grades are either 'A' or 'B'. The IN operator checks if the grade column's value matches 'A' or 'B'.

Example 2: Select students whose city is not Mumbai or Bangalore

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

Output:

Explanation: This query retrieves students whose cities are neither 'Mumbai' nor 'Bangalore'. The NOT IN operator checks if the city column's value does not match 'Mumbai' or 'Bangalore'.

Example 3: Select students aged 18, 20, or 22

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

Output:

Explanation: This query retrieves students whose ages are either 18, 20, or 22. The IN operator checks if the age column's value matches any of the specified values (18, 20, 22).

Conclusion

The MySQL 'IN' operator is a powerful tool for filtering data by matching column values against a specified set. It simplifies complex queries and enhances readability, making it easier to manage and analyze data. By effectively utilizing the IN operator, you can simplify your SQL queries, improve performance, and handle multiple conditions with ease, making database management more efficient.