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

Last Updated : 19 Oct, 2021

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:

How to Use the IN Operator With a SubQuery?

romy421kumari

Improve

Article Tags :

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:T

2 min read

How to use the IN operator in SQLAlchemy in Python?

In this article, we will see how to use the IN operator using SQLAlchemy in Python. We will cover 2 examples, one each for SQLAchemy Core and ORM layers. In both examples, we will count the number of records present in the category table within the sakila database. The sample data from the table loo

4 min read

How to Update Table Rows in PL/SQL Using Subquery?

Updating table rows in PL/SQL via subqueries allows precise data modifications based on specific conditions. By integrating subqueries within the UPDATE statement, rows can be selectively targeted for updates, enhancing data management efficiency. This article explores the concept of updating rows i

4 min read

How to Update Table Rows in SQL Server using Subquery ?

Updating table rows in SQL Server using a subquery is a common operation that allows us to modify records based on values derived from another table or query. In this article, we will explain how to update table rows in SQL Server using subquery with the help of examples. Updating Table Rows Using a

3 min read

How to Use â€œNOT INâ€ Operator in R?

In this article, we will discuss NOT IN Operator in R Programming Language. NOT IN Operator is used to check whether the element in present or not. The symbol used for IN operator is "%in%". For NOT IN operator we have to add " ! " operator before that , so the symbol for NOT IN operator is "! %in%"

2 min read

How to Update Table Rows in SQLite Using Subquery

SQLite is a lightweight, serverless RDBMS that is used to store and manipulate the data in the database in relational or tabular form. It can run on various operating systems like Windows, macOS, Linux and mobile operating systems like Android and iOS. SQLite is designed for single-user access. Mult

4 min read

Subqueries in the WHERE Clause of UPDATE in SQL

In SQL, an UPDATE statement modifies existing records in a table. When updating records, we can use a subquery within the WHERE clause to conditionally update only specific rows based on results from another query. In this article, we will learn how to write Subqueries in the WHERE Clause to Update

4 min read

How to Update Table Rows in PostgreSQL Using Subquery?

PostgreSQL is a general-purpose object-relational database management system and is open-source. It ensures data integrity features like constraints, transactions and foreign key support. In this article, We will understand How to update table rows in PostgreSQL using subquery using various methods,

5 min read

How to Update Table Rows Using Subquery in MySQL

Updating table rows using subqueries in MySQL enables precise modifications based on specific conditions or values from other tables. This technique leverages subqueries within the SET or WHERE clauses of the UPDATE statement, allowing dynamic and context-specific updates. This guide covers the synt

5 min read

How to Get Multiple Counts With Single Query in MySQL

MySQL is an open-source Relational Database Management System that stores data in a structured format using rows and columns. MYSQL language is easy to use as compared to other programming languages like C, C++, Java, etc. By learning some basic commands we can work, create, and interact with the Da

5 min read

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

Similar Reads