How to Use the IN Operator With a SubQuery?
Last Updated :
12 Dec, 2021
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
Share your thoughts in the comments
Please Login to comment...