SQL IN Operator
- IN is an operator in SQL, which is generally used with a WHERE clause.
- Using the IN operator, multiple values can be specified.
- It allows us to easily test if an expression matches any value in a list of values.
- IN operator is used to replace many OR conditions.
Syntax of IN operator in SQL:
Now let us take a deeper dive into the IN operator in SQL with the help of examples. All the queries in the examples will be written using the MySQL database.
Consider we have t_students table with the following data:
| ID | Name | Hometown | Percentage | Favourite_Subject |
|---|---|---|---|---|
| 1 | Soniya Jain | Udaipur | 89 | Physics |
| 2 | Harshada Sharma | Kanpur | 92 | Chemistry |
| 3 | Anuja Rajput | Jaipur | 78 | History |
| 4 | Pranali Singh | Nashik | 88 | Geography |
| 5 | Renuka Deshmukh | Panipat | 90 | Biology |
| 6 | Swati Kumari | Faridabad | 93 | English |
| 7 | Prachi Singh | Gurugram | 96 | Hindi |
| 8 | Sheetal Sonar | Jaipur | 93 | Physics |
| 9 | Erica Goenka | Faridabad | 92 | Biology |
| 10 | Monali Chaudhari | Panipat | 90 | History |
Example 1:
Write a query to display all the records from the t_students table where the hometown of the students is one of the following places: Faridabad, Panipat, or Jaipur.
Query:
Here, we have written a SELECT query with a WHERE clause on the Hometown column followed by IN operator. All the places which are allowed in the Hometown column, i.e., Faridabad, Panipat, or Jaipur, are passed as a parameter to the IN operator. So, only those students from the t_students table whose hometown is one of the places which are passed to the IN operator will be considered in the output.
You will get the following output:
| ID | Name | Hometown | Percentage | Favourite_Subject |
|---|---|---|---|---|
| 3 | Anuja Rajput | Jaipur | 78 | History |
| 5 | Renuka Deshmukh | Panipat | 90 | Biology |
| 6 | Swati Kumari | Faridabad | 93 | English |
| 8 | Sheetal Sonar | Jaipur | 93 | Physics |
| 9 | Erica Goenka | Faridabad | 92 | Biology |
| 10 | Monali Chaudhari | Panipat | 90 | History |
There are six students in the t_students table whose hometown is one of the following places: Faridabad, Panipat, or Jaipur.
Example 2:
Write a query to display all the records from the t_students table where the favourite subject of the students is one of the following subjects: History, Biology, Physics or Chemistry.
Query:
Here, we have written a SELECT query with a WHERE clause on the Favourite_Subject column followed by IN operator. All the subjects which are allowed in the Favourite_Subject column, i.e., History, Biology, Physics, or Chemistry, are passed as a parameter to the IN operator. So, only those students from the t_students table whose favourite subject is one of the subjects which are passed to the IN operator will be considered in the output.
You will get the following output:
| ID | Name | Hometown | Percentage | Favourite_Subject |
|---|---|---|---|---|
| 1 | Soniya Jain | Udaipur | 89 | Physics |
| 2 | Harshada Sharma | Kanpur | 92 | Chemistry |
| 3 | Anuja Rajput | Jaipur | 78 | History |
| 5 | Renuka Deshmukh | Panipat | 90 | Biology |
| 8 | Sheetal Sonar | Jaipur | 93 | Physics |
| 9 | Erica Goenka | Faridabad | 92 | Biology |
| 10 | Monali Chaudhari | Panipat | 90 | History |
There are seven students in the t_students table whose favourite subject is one of the following subjects: History, Biology, Physics or Chemistry.
Example 3:
Write a query to display all the records from the t_students table where the percentage secured by the student is one of the following values: 78, 88, 89, 90, or 92.
Query:
Here, we have written a SELECT query with a WHERE clause on the Percentage column followed by IN operator. All the values which are allowed in the Percentage column, i.e., 78, 88, 89, 90, or 92, are passed as a parameter to the IN operator. So, only those students from the t_students table who have secured one of the percentage values which are passed to the IN operator will be considered in the output.
You will get the following output:
| ID | Name | Hometown | Percentage | Favourite_Subject |
|---|---|---|---|---|
| 1 | Soniya Jain | Udaipur | 89 | Physics |
| 2 | Harshada Sharma | Kanpur | 92 | Chemistry |
| 3 | Anuja Rajput | Jaipur | 78 | History |
| 4 | Pranali Singh | Nashik | 88 | Geography |
| 5 | Renuka Deshmukh | Panipat | 90 | Biology |
| 9 | Erica Goenka | Faridabad | 92 | Biology |
| 10 | Monali Chaudhari | Panipat | 90 | History |
There are seven students in the t_students table who have secured one of the following percentage values: 78, 88, 89, 90, or 92.
Consider we have another table employee with the following data:
| ID | Name | Date_of_Birth | Department | Salary | Job_Location |
|---|---|---|---|---|---|
| 1 | Mansi Shah | 1999-01-10 | Marketing | 35000 | Surat |
| 2 | Tejal Wagh | 1993-07-05 | HR | 52000 | Pune |
| 3 | Sejal Kumari | 1995-06-08 | Production | 60000 | Nashik |
| 4 | Sonal Jain | 1996-12-10 | HR | 23000 | Surat |
| 5 | Surili Maheshwari | 1993-05-03 | Development | 75000 | Shimla |
| 6 | Shrusti Sharma | 1999-01-10 | Accounts | 53000 | Noida |
| 7 | Ankita Joshi | 1990-10-01 | RnD | 48000 | Delhi |
| 8 | Sharda Patel | 1989-12-12 | Marketing | 30000 | Delhi |
| 9 | Shivangi More | 1989-01-09 | Accounts | 67000 | Mumbai |
| 10 | Shweta Tiwari | 1981-10-09 | Purchasing | 60000 | Bangalore |
Example 1:
Write a query to display all the records from the employee table where the date of birth of an employee is one of the following dates: 1999-01-10, 1989-01-09, 1993-03-05, or 1993-05-03.
Query:
Here, we have written a SELECT query with a WHERE clause on the Date_of_Birth column followed by IN operator. All the dates which are allowed in the Date_of_Birth column, i.e., 1999-01-10, 1989-01-09, 1993-03-05, or 1993-05-03, are passed as a parameter to the IN operator. So, only those employees from the employee table whose date of birth matches with the dates passed to the IN operator will be considered in the output.
You will get the following output:
| ID | Name | Date_of_Birth | Department | Salary | Job_Location |
|---|---|---|---|---|---|
| 1 | Mansi Shah | 1999-01-10 | Marketing | 35000 | Surat |
| 5 | Surili Maheshwari | 1993-05-03 | Development | 75000 | Shimla |
| 6 | Shrusti Sharma | 1999-01-10 | Accounts | 53000 | Noida |
| 9 | Shivangi More | 1989-01-09 | Accounts | 67000 | Mumbai |
There are four employees in the employee table whose date of birth is either of the dates: 1999-01-10, 1989-01-09, 1993-03-05, or 1993-05-03.
Example 2:
Write a query to display all the records from the employee table where the department in which an employee is working is among one of the following departments: Purchasing, Accounts, Marketing, Production, or RnD.
Query:
Here, we have written a SELECT query with a WHERE clause on the Department column followed by IN operator. All the departments which are allowed in the Department column, i.e., Purchasing, Accounts, Marketing, Production, or RnD, are passed as a parameter to the IN operator. So, only those employees from the employee table who are working in the departments which are passed to the IN operator will be considered in the output.
You will get the following output:
| ID | Name | Date_of_Birth | Department | Salary | Job_Location |
|---|---|---|---|---|---|
| 1 | Mansi Shah | 1999-01-10 | Marketing | 35000 | Surat |
| 2 | Tejal Wagh | 1993-07-05 | HR | 52000 | Pune |
| 3 | Sejal Kumari | 1995-06-08 | Production | 60000 | Nashik |
| 4 | Sonal Jain | 1996-12-10 | HR | 23000 | Surat |
| 6 | Shrusti Sharma | 1999-01-10 | Accounts | 53000 | Noida |
| 7 | Ankita Joshi | 1990-10-01 | RnD | 48000 | Delhi |
| 8 | Sharda Patel | 1989-12-12 | Marketing | 30000 | Delhi |
| 9 | Shivangi More | 1989-01-09 | Accounts | 67000 | Mumbai |
| 10 | Shweta Tiwari | 1981-10-09 | Purchasing | 60000 | Bangalore |
There are nine employees in the employee table who are working in either of the departments: Purchasing, Accounts, Marketing, Production, or RnD.
Example 3:
Write a query to display all the records from the employee table where the job location of an employee is among one of the following places: Nashik, Surat, Noida, Delhi, or Pune.
Query:
Here, we have written a SELECT query with a WHERE clause on the Job_Location column followed by IN operator. All the places which are allowed in the Job_Location column, i.e., Nashik, Surat, Noida, Delhi, or Pune, are passed as a parameter to the IN operator. So, only those employees from the employee table whose job location is among the places which are passed to the IN operator will be considered in the output.
You will get the following output:
| ID | Name | Date_of_Birth | Department | Salary | Job_Location |
|---|---|---|---|---|---|
| 1 | Mansi Shah | 1999-01-10 | Marketing | 35000 | Surat |
| 2 | Tejal Wagh | 1993-07-05 | HR | 52000 | Pune |
| 3 | Sejal Kumari | 1995-06-08 | Production | 60000 | Nashik |
| 4 | Sonal Jain | 1996-12-10 | HR | 23000 | Surat |
| 6 | Shrusti Sharma | 1999-01-10 | Accounts | 53000 | Noida |
| 7 | Ankita Joshi | 1990-10-01 | RnD | 48000 | Delhi |
| 8 | Sharda Patel | 1989-12-12 | Marketing | 30000 | Delhi |
There are seven employees in the employee table whose job location is either Nashik, Surat, Noida, Delhi, or Pune.
Example 4:
Write a query to display all the records from the employee table where the salary of an employee is among one of the following values: 60000, 53000, 30000, or 45000.
Query:
Here, we have written a SELECT query with a WHERE clause on the Salary column followed by IN operator. All the values which are allowed in the Salary column, i.e., 60000, 53000, 30000, or 45000, are passed as a parameter to the IN operator. So, only those employees from the employee table whose salary is among the values which are passed to the IN operator will be considered in the output.
You will get the following output:
| ID | Name | Date_of_Birth | Department | Salary | Job_Location |
|---|---|---|---|---|---|
| 3 | Sejal Kumari | 1995-06-08 | Production | 60000 | Nashik |
| 6 | Shrusti Sharma | 1999-01-10 | Accounts | 53000 | Noida |
| 8 | Sharda Patel | 1989-12-12 | Marketing | 30000 | Delhi |
| 10 | Shweta Tiwari | 1981-10-09 | Purchasing | 60000 | Bangalore |
There are four employees in the employee table whose salary is either 60000, 53000, 30000 or 45000.