SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL OPERATORS SQL COMMANDS SQL Queries

SQL Table

SQL TABLE SQL CREATE TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL UPDATE TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT INTO Statement

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

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

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65500PUNEORACLE1
1002VAIBHAVSHARMA60000NOIDAC#5
1003NIKHILVANI50500JAIPURFMW2
2001PRACHISHARMA55500CHANDIGARHORACLE1
2002BHAVESHJAIN65500PUNEFMW2
2003RUCHIKAJAIN50000MUMBAIC#5
3001PRANOTISHENDE55500PUNEJAVA3
3002ANUJAWANRE50500JAIPURFMW2
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4001RAJESHGOUD60500MUMBAITESTING4
4002ASHWINIBAGHAT54500NOIDAJAVA3
4003RUCHIKAAGARWAL60000DELHIORACLE1
5001ARCHITSHARMA55500DELHITESTING4

Table 2: Manager

Manageridmanager_namemanager_department
1Snehdeep KaurORACLE
2Kirti KirtaneFMW
3Abhishek ManishJAVA
4Anupam MishraTESTING
5Akash KadamC#

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:

EMPLOYEEIDFIRST_NAMELAST_NAMECITY
1001VAIBHAVIMISHRAPUNE
2002BHAVESHJAINPUNE
2003RUCHIKAJAINMUMBAI
3001PRANOTISHENDEPUNE
3003DEEPAMJAUHARIMUMBAI
4001RAJESHGOUDMUMBAI
4003RUCHIKAAGARWALDELHI
5001ARCHITSHARMADELHI
SQL SELECT IN

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:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYDEPARTMENT
1001VAIBHAVIMISHRA65500ORACLE
1003NIKHILVANI50500FMW
2001PRACHISHARMA55500ORACLE
2002BHAVESHJAIN65500FMW
3001PRANOTISHENDE55500JAVA
3002ANUJAWANRE50500FMW
5001ARCHITSHARMA55500TESTING
SQL SELECT IN

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:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITY
1001VAIBHAVIMISHRA65500PUNE
1003NIKHILVANI50500JAIPUR
2002BHAVESHJAIN65500PUNE
2003RUCHIKAJAIN50000MUMBAI
3001PRANOTISHENDE55500PUNE
3002ANUJAWANRE50500JAIPUR
3003DEEPAMJAUHARI58500MUMBAI
4001RAJESHGOUD60500MUMBAI
SQL SELECT IN

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:

EMPLOYEEIDSALARYCITYDEPARTMENT
100165500PUNEORACLE
200265500PUNEFMW
400360000DELHIORACLE
SQL SELECT IN

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:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENT
1001VAIBHAVIMISHRA65500PUNEORACLE
1002VAIBHAVSHARMA60000NOIDAC#
2002BHAVESHJAIN65500PUNEFMW
2003RUCHIKAJAIN50000MUMBAIC#
3001PRANOTISHENDE55500PUNEJAVA
3003DEEPAMJAUHARI58500MUMBAIJAVA
4001RAJESHGOUD60500MUMBAITESTING
4002ASHWINIBAGHAT54500NOIDAJAVA
5001ARCHITSHARMA55500DELHITESTING
SQL SELECT IN

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:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
3001PRANOTISHENDE55500PUNEJAVA3
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4002ASHWINIBAGHAT54500NOIDAJAVA3
4001RAJESHGOUD60500MUMBAITESTING4
5001ARCHITSHARMA55500DELHITESTING4
1002VAIBHAVSHARMA60000NOIDAC#5
2003RUCHIKAJAIN50000MUMBAIC#5
SQL SELECT IN

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:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65500PUNEORACLE1
2001PRACHISHARMA55500CHANDIGARHORACLE1
4003RUCHIKAAGARWAL60000DELHIORACLE1
1003NIKHILVANI50500JAIPURFMW2
2002BHAVESHJAIN65500PUNEFMW2
3002ANUJAWANRE50500JAIPURFMW2
3001PRANOTISHENDE55500PUNEJAVA3
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4002ASHWINIBAGHAT54500NOIDAJAVA3
SQL SELECT IN

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:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITY
1001VAIBHAVIMISHRA65500PUNE
1002VAIBHAVSHARMA60000NOIDA
1003NIKHILVANI50500JAIPUR
2001PRACHISHARMA55500CHANDIGARH
2002BHAVESHJAIN65500PUNE
2003RUCHIKAJAIN50000MUMBAI
3001PRANOTISHENDE55500PUNE
3002ANUJAWANRE50500JAIPUR
3003DEEPAMJAUHARI58500MUMBAI
4001RAJESHGOUD60500MUMBAI
4002ASHWINIBAGHAT54500NOIDA
4003RUCHIKAAGARWAL60000DELHI
5001ARCHITSHARMA55500DELHI
SQL SELECT IN



ADVERTISEMENT
ADVERTISEMENT