SQL SELECT IN

SQL SELECT IN is a logical operator in Structured Query Language. It is used in SQL queries to reduce the use of multiple 'OR' operators. s

The IN operator in SQL also allows to easily test the condition matches any value in a list of values. It reduces the number of OR operators in SQL queries.

Syntax of IN operator in SQL:

SELECT COLUMNNAME FROM TABLENAME WHERE COLUMNNAME IN (VALUE1, VALUE2);

Subquery Syntax of IN operator in SQL:

SELECT COLUMNNAME FROM TABLENAME WHERE COLUMNNAME IN (SELECT STATEMENT); 

In the above syntax, we can use IN operator with subquery also.

Let’s understand the SQL SELECT IN concept with the help of examples.

Consider the following tables which have the following records:

Table 1: Employees

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE	1
1002	VAIBHAV	SHARMA	60000	NOIDA	C#	5
1003	NIKHIL	VANI	50500	JAIPUR	FMW	2
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1
2002	BHAVESH	JAIN	65500	PUNE	FMW	2
2003	RUCHIKA	JAIN	50000	MUMBAI	C#	5
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3
3002	ANUJA	WANRE	50500	JAIPUR	FMW	2
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	4
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA	3
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE	1
5001	ARCHIT	SHARMA	55500	DELHI	TESTING	4

Table 2: Manager

Managerid	manager_name	manager_department
1	Snehdeep Kaur	ORACLE
2	Kirti Kirtane	FMW
3	Abhishek Manish	JAVA
4	Anupam Mishra	TESTING
5	Akash Kadam	C#

Example 1:  Write a query to fetch the employee id, first name, last name, and City from the employee's table where City includes one of the lists is Mumbai, Pune, and Delhi in the table.

SELECT EMPLOYEEID, FIRST_NAME, LAST_NAME, CITY FROM EMPLOYEES WHERE CITY IN ('MUMBAI', 'PUNE', 'DELHI');

In the above statement, we have retrieved the employee id, first and last name, and City from the employee's table, where City includes one of the city names, Mumbai, Pune, and Delhi. All these cities names are passed as a parameter in the IN operator as values. The table will go for all those records whose employees' cities match the IN operator parameter city list.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	CITY
1001	VAIBHAVI	MISHRA	PUNE
2002	BHAVESH	JAIN	PUNE
2003	RUCHIKA	JAIN	MUMBAI
3001	PRANOTI	SHENDE	PUNE
3003	DEEPAM	JAUHARI	MUMBAI
4001	RAJESH	GOUD	MUMBAI
4003	RUCHIKA	AGARWAL	DELHI
5001	ARCHIT	SHARMA	DELHI

It shows only eight employees whose city names match the IN operator expression parameter list.

Example 2: Write a query to fetch the employee id, first name, last name, salary, and Department from the employee's table where salary includes one of the lists is 50500, 55500, and 65500 in the table.

SELECT EMPLOYEEID, FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT FROM EMPLOYEES WHERE SALARY IN (50500, 55500, 65500);

In the above statement, we have retrieved the employee id, first and last name, salary, and Department from the employee's table where salary includes one of the salaries are 50500, 55500, and 65500. All these salaries are passed as a parameter in the IN operator as values. The table will go for all those records whose employees' salaries match the IN operator parameter salary list.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	DEPARTMENT
1001	VAIBHAVI	MISHRA	65500	ORACLE
1003	NIKHIL	VANI	50500	FMW
2001	PRACHI	SHARMA	55500	ORACLE
2002	BHAVESH	JAIN	65500	FMW
3001	PRANOTI	SHENDE	55500	JAVA
3002	ANUJA	WANRE	50500	FMW
5001	ARCHIT	SHARMA	55500	TESTING

It shows only seven employees whose salary matches the IN operator expression parameter list.

Example 3: Write a query to fetch the employee id, first name, last name, salary, and City from employees where employee salary is greater than 60000 or employee city includes one of the city lists is 'Mumbai', 'Pune' and, 'Jaipur' in the table.

SELECT EMPLOYEEID, FIRST_NAME, LAST_NAME, SALARY, CITY FROM EMPLOYEES WHERE SALARY > 60000 OR CITY IN ('PUNE', 'MUMBAI', 'JAIPUR');

In the above statement, we have fetched employee id, first name, last name, salary, and City from employees table where employee salary is greater than 60000 or employee city includes one of this city list is 'Mumbai', 'Pune', 'Jaipur'. The SELECT statement WHERE clause first part is SALARY > 60000 and last part is CITY IN ('PUNE', 'MUMBAI', 'JAIPUR'), aswe used OR operator the result will be from both the conditions.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY
1001	VAIBHAVI	MISHRA	65500	PUNE
1003	NIKHIL	VANI	50500	JAIPUR
2002	BHAVESH	JAIN	65500	PUNE
2003	RUCHIKA	JAIN	50000	MUMBAI
3001	PRANOTI	SHENDE	55500	PUNE
3002	ANUJA	WANRE	50500	JAIPUR
3003	DEEPAM	JAUHARI	58500	MUMBAI
4001	RAJESH	GOUD	60500	MUMBAI

It shows only eight records from the table whose salary is greater than 60000 or City names match the IN operator expression parameter list.

Example 4: Write a query to retrieve employee id, Salary, City, and Department from employees table where employee department includes one of the lists is 'Oracle', 'FMW' and also City includes one of the lists is 'Delhi', 'Noida', 'Pune'.

SELECT EMPLOYEEID, SALARY, CITY, DEPARTMENT FROM EMPLOYEES WHERE DEPARTMENT IN ('ORACLE', 'FMW') AND CITY IN ('PUNE', 'NOIDA', 'DELHI');

We have retrieved the employee id, salary, City, and Department from the employee's table in the above statement. The employee department includes one of the department lists passed to the IN operator parameter. Employee city includes one of the city lists passed to the IN operator parameter, and the result includes only those employee details that match both the conditions.

Output:

EMPLOYEEID	SALARY	CITY	DEPARTMENT
1001	65500	PUNE	ORACLE
2002	65500	PUNE	FMW
4003	60000	DELHI	ORACLE

There are only three records from the Employees table whose employee city includes Pune, Delhi, and Noida, and Department includes Oracle, FMW. 

Example 5: Write a query to fetch employee id, first name, last name, salary, City, and Department from the employee's table where employee salary is greater than 60000 and City includes one of the lists in Pune, Jaipur, Mumbai or Department includes one of the lists is Java, Testing, C#.

SELECT EMPLOYEEID, FIRST_NAME, LAST_NAME, SALARY, CITY, DEPARTMENT FROM EMPLOYEES WHERE SALARY > 60000 AND CITY IN ('PUNE', 'MUMBAI', 'JAIPUR') OR DEPARTMENT IN ('JAVA', 'TESTING', 'C#');

In the above statement, we have used OR operator, AND operator, and multiple IN operator with the SELECT statement to fetch the employee id, first name, last name, salary, City, and Department from the employee's table. The SELECT query first fetches the employee records where salary > 60000 AND City IN ('Pune', 'Mumbai', Jaipur), only those employees. Both conditions are true, and at the end Department IN ('Java', 'Testing', 'C#'), this query will search in the first phase result that those employee records we retrieved having Department one of the lists we passed to the IN operator and also go for the rest of the records in the table if any records found having department name we passed to the IN operator parameter that record will be added to the result.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE
1002	VAIBHAV	SHARMA	60000	NOIDA	C#
2002	BHAVESH	JAIN	65500	PUNE	FMW
2003	RUCHIKA	JAIN	50000	MUMBAI	C#
3001	PRANOTI	SHENDE	55500	PUNE	JAVA
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA
4001	RAJESH	GOUD	60500	MUMBAI	TESTING
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA
5001	ARCHIT	SHARMA	55500	DELHI	TESTING

There are only 9 records of employees whose salary is greater than 60000. City includes one of the lists we passed as a parameter to the City IN operator or Department includes one of the lists we passed to the Department IN operator.

Example 6: Write a sub-query to fetch employee details from the employee table where managerid is greater than 2 from the manager table.

SELECT * FROM EMPLOYEES WHERE MANAGERID IN (SELECT MANAGERID FROM MANAGER WHERE MANAGERID > 2); 

In the above statement, the First subquery will get executed SELECT MANAGERID FROM MANAGER WHERE MANAGERID > 2; the output will be manager id which is greater than 2 pass as a parameter in the main query WHERE clause, and the final output will be from employees table where employee-manager id includes one of the lists which is the output of sub-query.

Output:

There are only seven records from the employee's table whose manager id is greater than 2.

Example 7: Write a sub-query to fetch the employee’s details where the manager department includes one of the lists is an oracle, java, and FMW.

SELECT * FROM EMPLOYEES WHERE MANAGERID IN (SELECT MANAGERID FROM MANAGER WHERE MANAGER_DEPARTMENT IN ('ORACLE', 'FMW', 'JAVA'));

In the above statement, we first fetch the manager id from the manager table where the manager department includes one of the lists is Oracle, FMW, Java. Then the main query will fetch the employee's details from the output of the sub-query.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE	1
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE	1
1003	NIKHIL	VANI	50500	JAIPUR	FMW	2
2002	BHAVESH	JAIN	65500	PUNE	FMW	2
3002	ANUJA	WANRE	50500	JAIPUR	FMW	2
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA	3

Example 8: Write a query to fetch employee id, first name, last name, salary, City from the employee's table where salary is between 50000 and 65000 or City includes one of the lists is Pune, Jaipur, and Mumbai.

SELECT EMPLOYEEID, FIRST_NAME, LAST_NAME, SALARY, CITY FROM EMPLOYEES WHERE SALARY BETWEEN 50000 AND 65000 OR CITY IN ('PUNE', 'MUMBAI', 'JAIPUR');

In the above statement, we have fetched the employee id, first name, last name, salary, and City from the employee's table where employee salary between 50000 and 65000 or the City includes one of the lists is Pune, Mumbai, Jaipur.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY
1001	VAIBHAVI	MISHRA	65500	PUNE
1002	VAIBHAV	SHARMA	60000	NOIDA
1003	NIKHIL	VANI	50500	JAIPUR
2001	PRACHI	SHARMA	55500	CHANDIGARH
2002	BHAVESH	JAIN	65500	PUNE
2003	RUCHIKA	JAIN	50000	MUMBAI
3001	PRANOTI	SHENDE	55500	PUNE
3002	ANUJA	WANRE	50500	JAIPUR
3003	DEEPAM	JAUHARI	58500	MUMBAI
4001	RAJESH	GOUD	60500	MUMBAI
4002	ASHWINI	BAGHAT	54500	NOIDA
4003	RUCHIKA	AGARWAL	60000	DELHI
5001	ARCHIT	SHARMA	55500	DELHI