How to use SQL IN Operator
In the previous article, we have discussed LIKE operator. In this article, we will discuss IN operator in SQL that is also a logical operator.
Table of Content
Best-suited Data Analytics courses for you
Learn Data Analytics with these high-rated online courses
What is an IN operator?
IN operator is a logical operator in SQL that is used to select the rows whose values are in a set of values i.e. IN operator allows you to specify a list of values that you want to include in your result.
- It is used with the WHERE clause
- It reduces the need for multiple OR clauses (condition)
- It can be used with subqueries
Must Read: SQL LIKE
Must Read: SQL BETWEEN
Syntax
SELECT column_namesFROM table_namesWHERE column_name IN (value_1, value_2, value_3,.....,value_n);
Syntax of IN operator for subquery
SELECT column_namesFROM table_nameWHERE condition IN (SELECT query);
Now, we will take some examples to get a better understanding of how to use IN operator in SQL
Here, we will take the Employee (that contains Employee ID, Name, Gender, Department, Education, Month of Joining, and CTC) and Department Dataset (DepartmentID and Department Name) to do some examples to get a clear understanding of different logical operators.
| EmployeeID | Name | Gender | DepartmentID | Education | Month of Joining | CTC(in Lacs) |
| 1001 | Ajay | M | 1 | Doctoral | January | 25 |
| 1002 | Babloo | M | 1 | UG | February | 23 |
| 1003 | Chhavi | F | 2 | P | March | 15 |
| 1004 | Dheeraj | M | 2 | UG | January | 12 |
| 1005 | Evina | F | 3 | UG | March | 16 |
| 1006 | Fredy | M | 4 | UG | December | 10 |
| 1007 | Garima | F | 4 | PG | March | 10 |
| 1008 | Hans | M | 5 | PG | November | 8 |
| 1009 | Ivanka | F | 5 | Intermediate | June | 7 |
| 1010 | Jai | M | 6 | December | 4 |
| DepartmentID | Department Name |
| 1 | Engineering |
| 2 | HR |
| 3 | Marketing |
| 4 | Sales |
| 5 | Admin |
| 6 | Peon |
Must Read: Introduction to SQL
Must Read: SQL ACID Properties
IN operator with Numeric Values
Example 1: Find the employee id, name, and department where CTC is 15, 12, and 10 lacs.
Query
SELECT EmployeeID, Name, DepartmentIDFROM EmployeeWHERE CTC IN (15, 12, 10);
Output
| EmployeeID | Name | DepartmentID |
| 1003 | Chhavi | 2 |
| 1004 | Dheeraj | 2 |
| 1006 | Fredy | 4 |
| 1007 | Garima | 4 |
Must Read: Difference between SQL and NoSQL
IN operator with string
Example 2: Find the EmployeeID, Name, and Month of Joining who join in the month of January, February, and November.
Query
SELECT EmployeeID, Name, Month of JoiningFROM EmployeeWHERE Month of Joining IN (‘January’, ‘February’, ‘November’);
Output
| EmployeeID | Name | Month of Joining |
| 1001 | Ajay | January |
| 1002 | Babloo | February |
| 1004 | Dheeraj | January |
| 1008 | Hans | November |
Must Read: Subqueries in SQL
Must Read: SQL CREATE TABLE
OR vs IN
Example 3: In this example, we will take the same example 2 but write the query using OR.
Find the EmployeeID, Name, and Month of Joining who join in the month of January, February, and November using OR.
Query
SELECT EmployeeID, Name, Month of JoiningFROM EmployeeWHERE Month of Joining = ‘January’ ORMonth of Joining = ‘February’ ORMonth of Joining = ‘November’;
Output
| EmployeeID | Name | Month of Joining |
| 1001 | Ajay | January |
| 1002 | Babloo | February |
| 1004 | Dheeraj | January |
| 1008 | Hans | November |
Must Read: SQL WHERE
Must Read: Introduction to Normalization
NOT with IN
Example 4: Find the name, education, and CTC of all the employees except those who are not belonging to departmentID 2, and 4 .
Query
SELECT Name, Education, CTCFROM EmployeeWHERE DepartmentID NOT IN (2, 4);
Output
| Name | Education | CTC |
| Ajay | Doctoral | 25 |
| Babloo | UG | 23 |
| Evina | UG | 16 |
| Hans | PG | 8 |
| Ivanka | Intermediate | 7 |
| Jai | 4 |
Must Read: SQL LIMITS
Must Check: SQL Online Course and Certifications
IN operator with Subquery
Query
SELECT EmployeeID, Name, GenderFROM EmployeeWHERE DepartmentID IN (SELECT Department Name FROM Department WHERE DepartmentID > 3);
Output
| EmployeeID | Name | Gender | Department Name |
| 1006 | Fredy | M | Sales |
| 1007 | Garima | F | Sales |
| 1008 | Hans | M | Admin |
| 1009 | Ivanka | F | Admin |
| 1010 | Jai | M | Peon |
Must Read: SQL Comparison and Arithmetic Operator
Conclusion
In this article, we have discussed how to use IN operator in sql with the help of examples.
Hope this article, will help you in Data Science/Data Analysis journey.
Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio
Top Picks & New Arrivals
