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
id | name | age | grade | city |
---|
1 | Amit Sharma | 18 | A | Delhi |
2 | Priya Singh | 19 | B | Mumbai |
3 | Raj Patel | 20 | A | Ahmedabad |
4 | Sneha Reddy | 21 | C | Hyderabad |
5 | Arjun Rao | 22 | B | Bangalore |
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:
id | name | grade | city |
---|
1 | Amit Sharma | A | Delhi |
2 | Priya Singh | B | Mumbai |
3 | Raj Patel | A | Ahmedabad |
5 | Arjun Rao | B | Bangalore |
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:
id | name | city |
---|
1 | Amit Sharma | Delhi |
3 | Raj Patel | Ahmedabad |
4 | Sneha Reddy | Hyderabad |
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:
id | name | age | city |
---|
1 | Amit Sharma | 18 | Delhi |
3 | Raj Patel | 20 | Ahmedabad |
5 | Arjun Rao | 22 | Bangalore |
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.
Similar Reads
SQL Tutorial Structured Query Language (SQL) is the standard language used to interact with relational databases. Whether you want to create, delete, update or read data, SQL provides the structure and commands to perform these operations. SQL is widely supported across various database systems like MySQL, Oracl
8 min read
SQL Commands | DDL, DQL, DML, DCL and TCL Commands SQL commands are crucial for managing databases effectively. These commands are divided into categories such as Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), Data Query Language (DQL), and Transaction Control Language (TCL). In this article, we will e
7 min read
Non-linear Components In electrical circuits, Non-linear Components are electronic devices that need an external power source to operate actively. Non-Linear Components are those that are changed with respect to the voltage and current. Elements that do not follow ohm's law are called Non-linear Components. Non-linear Co
11 min read
Spring Boot Tutorial Spring Boot is a Java framework that makes it easier to create and run Java applications. It simplifies the configuration and setup process, allowing developers to focus more on writing code for their applications. This Spring Boot Tutorial is a comprehensive guide that covers both basic and advance
10 min read
Normal Forms in DBMS In the world of database management, Normal Forms are important for ensuring that data is structured logically, reducing redundancy, and maintaining data integrity. When working with databases, especially relational databases, it is critical to follow normalization techniques that help to eliminate
7 min read
Class Diagram | Unified Modeling Language (UML) A UML class diagram is a visual tool that represents the structure of a system by showing its classes, attributes, methods, and the relationships between them. It helps everyone involved in a projectâlike developers and designersâunderstand how the system is organized and how its components interact
12 min read
Python Variables In Python, variables are used to store data that can be referenced and manipulated during program execution. A variable is essentially a name that is assigned to a value. Unlike many other programming languages, Python variables do not require explicit declaration of type. The type of the variable i
6 min read
Spring Boot Interview Questions and Answers Spring Boot is a Java-based framework used to develop stand-alone, production-ready applications with minimal configuration. Introduced by Pivotal in 2014, it simplifies the development of Spring applications by offering embedded servers, auto-configuration, and fast startup. Many top companies, inc
15+ min read
Backpropagation in Neural Network Back Propagation is also known as "Backward Propagation of Errors" is a method used to train neural network . Its goal is to reduce the difference between the modelâs predicted output and the actual output by adjusting the weights and biases in the network.It works iteratively to adjust weights and
9 min read
Polymorphism in Java Polymorphism in Java is one of the core concepts in object-oriented programming (OOP) that allows objects to behave differently based on their specific class type. The word polymorphism means having many forms, and it comes from the Greek words poly (many) and morph (forms), this means one entity ca
7 min read