SQL IN Operator

The SQL IN operator filters data based on a list of specific values. In general, we can only use one condition in the WHEN clause, but the IN operator allows us to specify multiple values.

In this article, we will learn about the IN operator in SQL by understanding its syntax and examples.

IN Operator

The IN Operator in SQL is used to specify multiple values/sub-queries in the WHERE clause. It provides an easy way to handle multiple OR conditions.

We only pass a single condition in the WHERE clause, however there might be situations where we need to select data based on multiple conditions. For such cases, the IN operator is used.

Note: If any of the conditions are passed using the IN operator, they will be considered true

SQL IN Syntax

The Syntax of the IN operator is as follows:

SELECT column_name FROM table_name

WHERE condition IN (condition_value1, condition_value2 …..);

Here, we select the column column_name from the table table_name where the condition is checked in all the condition_values passed with the IN operator.

DEMO SQL Database

We will use the following SQL table for our examples below:

Fname	Lname	Ssn	Bdate	Address	Sex	Salary
Chiranjeev	Singh	1	2002-07-31	Delhi	M	1111789.00
Harry	Stark	2	1990-07-31	Delhi	M	3333.00
Meghna	Gururaani	5	2002-04-04	Almora	F	3433.00
Aniket	Bhardwaj	6	2001-05-05	Ponta	M	56564.00
Vritti	Goel	7	2002-03-05	Delhi	F	7565.00
Aashish	Kumar	8	2002-08-04	Himachal	M	44657.00
Siddharth	Chaturvedi	9	2003-11-10	Lucknow	M	244322.00

You can create the following table using these queries:

MySQL

CREATE TABLE Employee (
    Fname VARCHAR(50),
    Lname VARCHAR(50),
    Ssn INT,
    Bdate DATE,
    Address VARCHAR(100),
    Sex CHAR(1),
    Salary DECIMAL(10, 2)
);
INSERT INTO Employee (Fname, Lname, Ssn, Bdate, Address, Sex, Salary) VALUES 
('Chiranjeev', 'Singh', 1, '2002-07-31', 'Delhi', 'M', 1111789.00),
('Harry', 'Stark', 2, '1990-07-31', 'Delhi', 'M', 3333.00),
('Meghna', 'Gururaani', 5, '2002-04-04', 'Almora', 'F', 3433.00),
('Aniket', 'Bhardwaj', 6, '2001-05-05', 'Ponta', 'M', 56564.00),
('Vritti', 'Goel', 7, '2002-03-05', 'Delhi', 'F', 7565.00),
('Aashish', 'Kumar', 8, '2002-08-04', 'Himachal', 'M', 44657.00),
('Siddharth', 'Chaturvedi', 9, '2003-11-10', 'Lucknow', 'M', 244322.00);

SQL IN Operator Examples

Let’s look at some examples of IN operator in SQL and understand its working.

Example 1:

SQL Query to get Fname and Lname of employees who have address in Delhi and Himachal

Query:

SELECT Fname, Lname FROM employee
WHERE Address IN ('Delhi','Himachal');

Output:

Fname	Lname
Chiranjeev	Singh
Harry	Stark
Vritti	Goel
Aashish	Kumar

In this query, we fetched the Fname and Lname of all the employees whose address is either Delhi or Himachal. To do so, we use the IN operator to pass these values to the WHERE clause.

Example 2: SQL IN and NOT IN Operators

We can use the SQL IN with the NOT operator to exclude specified data from our result.

Query:

SELECT Fname FROM employee
WHERE Address NOT IN ('Delhi', 'Lucknow');

Output:

Fname
Meghna
Aniket
Aashish

Here, we have created a query to fetch the Fname of all employees whose address is neither Delhi nor Lucknow. We used the NOT operator with IN to exclude these values.

Example 3:

We have used IN operator with explicit values for conditions. However, we can use the IN operator to select values for the condition from another query. For demonstrating this, we will use another table from the database, manager. The contents of the table are given below.

Ssn	Department
5	Sales
1	Technical

Now, we will write a query to fetch details of all employees who are managers. This can be done by using nested SELCT queries with the IN operator.

Query:

SELECT * FROM employee
WHERE Ssn IN (SELECT Ssn FROM manager);

Output:

Fname	Lname	Ssn	Bdate	Address	Sex	Salary
Chiranjeev	Singh	1	2002-07-31	Delhi	M	1111789.00
Meghna	Gururaani	5	2002-04-04	Almora	F	3433.00

Here, we have selected the Ssn of all managers from the manager table and then, we have passed the result of this query to the main query in the IN operator, which will fetch the details of all employees who have same Ssn as fetched from the nested query.

Key takeaways about the SQL IN operator:

• The SQL IN operator allows you to specify multiple values in a WHERE clause.
• It checks if a specified value matches any value in a list.
• It simplifies querying for records that match multiple criteria without needing to use multiple OR conditions.
• The syntax is straightforward: WHERE column_name IN (value1, value2, ...).
• It is commonly used with SELECT, INSERT, UPDATE, and DELETE statements for filtering or updating data based on multiple values.
• Using the IN operator can make SQL queries more concise and readable.