Home » SQL IN Operator

SQL IN Operator

by Online Tutorials Library

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.


You may also like