SQL SELECT IN

SQL SELECT IN operator allows to test if an expression matches any values in a list of values. This is generally used to reduce the use of multiple OR conditions in a SELECT, INSERT, UPDATE and DELETE statement.

SQL SELECT IN Syntax

The syntax of SQL SELECT IN is as follows.

expression IN (value1, value2, ....,valueN);

or 

expression IN (subquery);

In this syntax,

  • expression – The value to be test. 
  • value1, value2,…,valueN – These are the values to test against expression. If any of these values matches expression, then the IN condition will evaluate to true.
  • subquery – This is a SELECT statement whose result set will be tested against expression. If any of these values matches expression, then the IN condition will evaluate to true.

SQL SELECT IN Example

Let’s consider the below Employee table for our reference and example.

emp_id emp_name emp_gender dept_code emp_location
1510 Avinash Sirsath M 101 Mumbai
1511 Ramjan Ali M 105 Kolkata
1512 Piyanka Saha F 103 Kolkata
1513 Piyam mondal M 101 Kolkata
1514 Jitu Garg M 104 Mumbai
1515 Raman Sahani M 105 Delhi
1516 Lata Malhotra F 104 Mumbai
1517 Prakash Javedkar M 101 Delhi
1518 Nitu Rani F 103 Mumbai
1519 Pallab Das M 103 Kolkata
1520 Pankaj Sharma M 101 Bangalore

Now let’s take an example of IN operator which use the above table.

SELECT * FROM Employee
WHERE emp_id IN (1510,1511,1513,1514)
ORDER BY emp_id;
emp_id emp_name emp_gender dept_code emp_location
1510 Avinash Sirsath M 101 Mumbai
1511 Ramjan Ali M 105 Kolkata
1513 Piyam mondal M 101 Kolkata
1514 Jitu Garg M 104 Mumbai

Let’s check another example where character values are supplied in the IN operator.

SELECT * FROM Employee
WHERE emp_location IN ('Mumbai', 'Delhi')
ORDER BY emp_id;

Output of the the above query is as below.

emp_id emp_name emp_gender dept_code emp_location
1510 Avinash Sirsath M 101 Mumbai
1514 Jitu Garg M 104 Mumbai
1515 Raman Sahani M 105 Delhi
1516 Lata Malhotra F 104 Mumbai
1517 Prakash Javedkar M 101 Delhi
1518 Nitu Rani F 103 Mumbai

Summary: In this tutorial, you have learned how to use IN operator in SQL with syntax and examples.

Leave a Comment