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:

SELECT ColumnName FROM TableName WHERE ColumnName IN (Value 1, Value 2,…., ValueN);

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 Jaiswal	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:

mysql> SELECT *FROM t_students WHERE Hometown IN ("Faridabad", "Panipat", "Jaipur");

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:

mysql> SELECT *FROM t_students WHERE Favourite_Subject IN ("History", "Biology", "Physics", "Chemistry");

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:

mysql> SELECT *FROM t_students WHERE Percentage IN (78, 88, 89, 90, 92);

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:

mysql> SELECT *FROM employee WHERE Date_of_Birth IN ("1999-01-10", "1989-01-09", "1993-03-05", "1993-05-03");

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:

mysql> SELECT *FROM employee WHERE Department IN ("Purchasing", "Accounts", "Marketing", "HR", "Production", "RnD");

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:

mysql> SELECT *FROM employee WHERE Job_location IN ("Nashik", "Surat", "Noida", "Delhi", "Pune");

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:

mysql> SELECT *FROM employee WHERE Salary IN (60000, 53000, 30000, 45000);

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.

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection

SQL String Functions

Miscl

SQL MCQ

PL/SQL Tutorial

Sql Interview Question

SQl Quiz

SQL IN Operator

Syntax of IN operator in SQL:

Example 1:

Example 2:

Example 3:

Example 1:

Example 2:

Example 3:

Example 4:

Comment / Reply From

You May Also Like

Popular Post

Language

Newsletter