How to Use the IN Operator with a SubQuery in SQL?

In this article, we will see the use of IN Operator with a SubQuery in SQL. IN operator is used to compare the column values with the list of values. The list of values is defined after IN query in SQL. 

If we don't know the exact list of values to be compared, we can generate the list of values using the query. 

Syntax:

Without subquery:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

with subquery

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT statement);

Now, for the demonstration follow the below steps:

Step 1: Create a database

we can use the following command to create a database called geeks.

Query:

CREATE DATABASE geeks;

Step 2: Use database

Use the below SQL statement to switch the database context to geeks:

USE geeks;

Step 3: Table definition

We have the following demo_table in our geek’s database. The demo_table has the student's data with their age and the grades obtained by them.

Query:

CREATE TABLE demo_table(
NAME VARCHAR(20),
AGE int,
GRADE VARCHAR(5));

Step 4: Insert data into a table

Query:

INSERT INTO demo_table
VALUES ('Romy',22, 'A'),
('Roshini', 20, 'A'),
('Akanksha', 22, 'A'),
('Shalini',23,'B'),
('Sambhavi',21,'A'),
('Meenakshi',22,'C'),
('Nikhil',24,'B'),
('Manu',25,'B'),
('Rohit',24,'C'),
('Astha',23,'A'),
('Samiksha',23,'A');

Step 5: View the content

Execute the below query to see the content of the table

SELECT * FROM demo_table;

Output:

Step 6: Use of In operator with a subquery

To get the data of students whose name starts with R.

Query:

SELECT * FROM demo_table
WHERE NAME IN  
(SELECT NAME FROM demo_table WHERE NAME LIKE 'R%') ;

Output:

To get the data of students whose age is more than 22.

Query:

SELECT * FROM demo_table
WHERE AGE IN  
(SELECT AGE FROM demo_table WHERE AGE>22) ;

Output:

romy421kumari

Follow

Improve

Article Tags :

• SQL
• TrueGeek
• TrueGeek-2021