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 geeks for geeks. After that, we will create a table inside it with the name geeks_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 geeksforgeeks;

Output:

Figure: Create database 

Step 2: Create a table inside the database 

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

• Creating geeks_data table :

Query :

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

Figure: Create a table geeks_data

• Creating geeks_dept table :

Query:

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

Figure: Create a table geeks_dept

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 geeks_data table.

Query: 

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

Figure: Insert values in geeks_data table

 Output:

Figure: geeks_data

Now we will insert data for the table geeks_dept.

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

Figure: Insert data into geeks_dept

Output:

Figure: geeks_dept 

Step 4: Executing IN Operator within a Sub Query

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

Query:

SELECT first_name, last_name FROM geeks_data WHERE dept IN 
                    (SELECT dept_name FROM geeks_dept WHERE id = 1);

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

Output:

Figure: Result of IN operator within Sub Query

ankur035

Follow

Improve

Article Tags :

• SQL
• SQL-Server