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:
EMPLOYEEID | FIRST_NAME | LAST_NAME | SALARY | CITY | DEPARTMENT | MANAGERID |
3001 | PRANOTI | SHENDE | 55500 | PUNE | JAVA | 3 |
3003 | DEEPAM | JAUHARI | 58500 | MUMBAI | JAVA | 3 |
4002 | ASHWINI | BAGHAT | 54500 | NOIDA | JAVA | 3 |
4001 | RAJESH | GOUD | 60500 | MUMBAI | TESTING | 4 |
5001 | ARCHIT | SHARMA | 55500 | DELHI | TESTING | 4 |
1002 | VAIBHAV | SHARMA | 60000 | NOIDA | C# | 5 |
2003 | RUCHIKA | JAIN | 50000 | MUMBAI | C# | 5 |

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 |
