How to Use the IN Operator With a SubQuery?

The IN Operator in SQL allows to specifies multiple values in WHERE clause, it can help you to easily test if an expression matches any value in the list of values. The use of IN Operator reduces the need for multiple OR conditions in statements like SELECT, INSERT, UPDATE, and DELETE. 

Sub Queries:

The SQL queries where one or more SELECT statements are nested with the WHERE clause of another SELECT statement are called subquery. The first statement of such type of query is called outer query where as the inside one is called an inner query. In the execution of such queries, the inner query will be evaluated first, and the outer query receives the value of the inner query.

Now, we will create a schema for our database and named it Beginner for Beginner. After that, we will create a table inside it with the name Beginner_data and use IN operator with a sub-query.

Step 1: Create a database

In order to create a database, we need to use the CREATE operator.

Query :

CREATE DATABASE w3wiki;

Output:

Step 2: Create a table inside the database 

In this step, we will create two tables Beginner_data and geek_dept inside the Beginner for Beginner database.

• Creating Beginner_data table :

Query :

CREATE TABLE Beginner_data(id INT, 
                        first_name VARCHAR(255),
                        last_name VARCHAR(255),
                        dept  VARCHAR(255),
                        PRIMARY KEY(id));

• Creating Beginner_dept table :

Query:

CREATE TABLE Beginner_dept(id INT, 
                        dept_name VARCHAR(255),
                        PRIMARY KEY(id));

Step 3: Insert data into the table

In order to insert the data inside the database, we need to use the INSERT operator. First, we will insert it in the Beginner_data table.

Query: 

INSERT INTO Beginner_data VALUES 
(1, 'Chandan', 'Mishra', 'Mechanical'),
(2, 'Abhinav', 'Singh', 'Electronics'),
(3, 'Utkarsh', 'Raghuvanshi', 'Computer Science');

 Output:

Now we will insert data for the table Beginner_dept.

INSERT INTO Beginner_dept VALUES (1, 'Computer Science'),
                              (2, 'Electronics'),
                              (3, 'Computer Science'),
                              (4, 'Mechanical');

Output:

Step 4: Executing IN Operator within a Sub Query

In this step we will try to find out the geek’s data from table Beginner_data, those who are from the computer science department with the help of Beginner_dept table using sub-query.

Query:

SELECT first_name, last_name FROM Beginner_data WHERE dept IN 
                    (SELECT dept_name FROM Beginner_dept WHERE id = 1);

We will get the first_name and last_name of the Beginner who are from the computer science department.

Output: