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.
SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);
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.
CREATE DATABASE geeks;
Step 2: Use database
Use the below SQL statement to switch the database context to 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.
CREATE TABLE demo_table( NAME VARCHAR(20), AGE int, GRADE VARCHAR(5));
Step 4: Insert data into a table
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;
Step 6: Use of In operator with a subquery
To get the data of students whose name starts with R.
SELECT * FROM demo_table WHERE NAME IN (SELECT NAME FROM demo_table WHERE NAME LIKE 'R%') ;
To get the data of students whose age is more than 22.
SELECT * FROM demo_table WHERE AGE IN (SELECT AGE FROM demo_table WHERE AGE>22) ;