SQL SubQuery
The Sub-query in the SQL is the inner query placed or positioned inside another query, which is also known is the outer query. The inner query is embedded in the Where clause of the SQL Query.
There are certain rules for executing the Subqueries in the SQL are as follows:
- We can use SQL Sub-query along with SELECT Statement, UPDATE Statement, INSERT Statement and DELETE Statement in the SQL statement.
- When we use Subqueries in the SQL statement, the nested inner query will get executed first, then the outer query, which is the main query executed at the end.
- A comparison operator can also be used.
- The inner query is closed within the parenthesis, and the inner query is placed on the right side of the comparison operator.
- You cannot use the ORDER BY clause in the Sub-query but can use the GROUP BY clause.
- The Sub-query with FROM clause can be used, WHERE clause, and HAVING clause.
Let's understand the SQL Sub-Query with the help of examples.
Consider the already existing table, which has the following data:
Table Number 1: - D_Students
Student_Id | Student_Name | First_Sem | Second_Sem | Third_Sem | Fourth_Sem | Fifth_Sem | Sixth_Sem | Total | Department_Id |
202111 | Vaishnavi Patil | 94 | 91 | 88 | 85 | 95 | 92 | 91 | 1 |
202112 | Vaibhav Lokhande | 85 | 90 | 92 | 80 | 85 | 82 | 86 | 2 |
202113 | Yash Dhull | 90 | 88 | 94 | 87 | 85 | 90 | 89 | 3 |
202114 | Sonali Patole | 95 | 90 | 92 | 88 | 92 | 90 | 91 | 4 |
202115 | Axar Patel | 85 | 80 | 82 | 86 | 92 | 84 | 85 | 1 |
202116 | Meena Mishra | 78 | 75 | 80 | 74 | 85 | 77 | 78 | 3 |
202117 | Mahesh Kumbhar | 75 | 80 | 75 | 78 | 80 | 76 | 77 | 5 |
202118 | Sakshi Patil | 80 | 78 | 74 | 78 | 80 | 77 | 78 | 2 |
202119 | Sopan Bhore | 70 | 68 | 75 | 75 | 80 | 80 | 75 | 2 |
202220 | Prajwal Lokhande | 80 | 85 | 85 | 75 | 78 | 80 | 81 | 4 |
202221 | Anuja Wanare | 85 | 88 | 86 | 82 | 84 | 85 | 85 | 5 |
202222 | Venkatesh Iyer | 90 | 89 | 87 | 90 | 92 | 91 | 90 | 3 |
202223 | Anushka Sen | 70 | 75 | 71 | 74 | 80 | 78 | 75 | 1 |
202224 | Aakash Jain | 80 | 75 | 72 | 74 | 85 | 80 | 78 | 4 |
202225 | Akshay Agarwal | 85 | 80 | 78 | 88 | 90 | 82 | 84 | 5 |
202226 | Shwetali Bhagwat | 90 | 80 | 85 | 88 | 90 | 80 | 86 | 1 |
202227 | Mayuri Wagh | 80 | 80 | 85 | 80 | 82 | 85 | 82 | 4 |
202228 | Utkarsh Rokade | 85 | 80 | 80 | 90 | 84 | 84 | 84 | 5 |
202229 | Manthan Koli | 85 | 75 | 84 | 78 | 82 | 80 | 81 | 2 |
202230 | Mayur Jain | 80 | 88 | 87 | 90 | 92 | 90 | 88 | 1 |
Table Number 2: Department
Department_Id | Department_Name |
1 | Computer Engineering |
2 | Information Technology |
3 | Mechanical Engineering |
4 | Automobile Engineering |
5 | Civil Engineering |
6 | Electrical Engineering |
7 | Electronics and Telecommunication Engineering |
8 | Chemical Engineering |
1. Subquery with the SELECT Statement
The SELECT Statement is used with the subqueries to display the data from the tables.
The syntax of the Subquery with the SELECT statement is as follows:
SELECT * FROM Table_Name WHERE Column_Name Expression Operator (SELECT * FROM Table_Name);
Example 1: Execute a query to display the student's information where student department names are 'Computer Engineering', 'Information Technology', and 'Automobile Engineering'.
SELECT * FROM D_Students WHERE Department_Id IN (SELECT Department_Id FROM Department WHERE Department_Name IN ('Computer Engineering', 'Information Technology','Automobile Engineering'));
In the above query example, we display the student's information where student department names are 'Computer Engineering', 'Information Technology', and 'Automobile Engineering'.
First, SELECT Department_Id FROM Department WHERE Department_Name IN ('Computer Engineering', 'Information Technology', 'Automobile Engineering'); gets executed, and the output of this inner query is
Department_Id |
1 |
2 |
4 |
As the inner query output is calculated, the output is represented as input for the main query. The main query is executed as SELECT * FROM D_Students WHERE Department_Id IN (1, 2, 4);
The output of the above query is as follows:

Student_Id | Student_Name | First_Sem | Second_Sem | Third_Sem | Fourth_Sem | Fifth_Sem | Sixth_Sem | Total | Department_Id |
202111 | Vaishnavi Patil | 94 | 91 | 88 | 85 | 95 | 92 | 91 | 1 |
202115 | Axar Patel | 85 | 80 | 82 | 86 | 92 | 84 | 85 | 1 |
202223 | Anushka Sen | 70 | 75 | 71 | 74 | 80 | 78 | 75 | 1 |
202226 | Shwetali Bhagwat | 90 | 80 | 85 | 88 | 90 | 80 | 86 | 1 |
202230 | Mayur Jain | 80 | 88 | 87 | 90 | 92 | 90 | 88 | 1 |
202112 | Vaibhav Lokhande | 85 | 90 | 92 | 80 | 85 | 82 | 86 | 2 |
202118 | Sakshi Patil | 80 | 78 | 74 | 78 | 80 | 77 | 78 | 2 |
202119 | Sopan Bhore | 70 | 68 | 75 | 75 | 80 | 80 | 75 | 2 |
202229 | Manthan Koli | 85 | 75 | 84 | 78 | 82 | 80 | 81 | 2 |
202114 | Sonali Patole | 95 | 90 | 92 | 88 | 92 | 90 | 91 | 4 |
202220 | Prajwal Lokhande | 80 | 85 | 85 | 75 | 78 | 80 | 81 | 4 |
202224 | Aakash Jain | 80 | 75 | 72 | 74 | 85 | 80 | 78 | 4 |
202227 | Mayuri Wagh | 80 | 80 | 85 | 80 | 82 | 85 | 82 | 4 |

Example 2: Execute a query to display the student’s information where the student’s department name is ‘Computer Engineering’
SELECT * FROM D_Students WHERE Department_Id = (SELECT Department_Id FROM Department WHERE Department_Name = 'Computer Engineering');
We display the student's information where the department name is 'Computer Engineering' in the above query. The query execution is the same as explained in the above example.
The output of the above query is as follows:
Student_Id | Student_Name | First_Sem | Second_Sem | Third_Sem | Fourth_Sem | Fifth_Sem | Sixth_Sem | Total | Department_Id |
202111 | Vaishnavi Patil | 94 | 91 | 88 | 85 | 95 | 92 | 91 | 1 |
202115 | Axar Patel | 85 | 80 | 82 | 86 | 92 | 84 | 85 | 1 |
202223 | Anushka Sen | 70 | 75 | 71 | 74 | 80 | 78 | 75 | 1 |
202226 | Shwetali Bhagwat | 90 | 80 | 85 | 88 | 90 | 80 | 86 | 1 |
202230 | Mayur Jain | 80 | 88 | 87 | 90 | 92 | 90 | 88 | 1 |

2. Subquery with the UPDATE Statement
The UPDATE statement is used with the subqueries to modify the data.
The Syntax of the Subquery with the UPDATE statement is as follows:
UPDATE Table_Name SET Column_Name = Values WHERE Column_Name Conditions operator (SELECT * FROM Table_Name WHERE Column_Name Conditions);
Example 1: Execute a query to modify the student's information where the student's first-semester percentage is greater than 85, and the department name is 'Computer Engineering'.
UPDATE D_Students SET DEpartment_Id = '6' WHERE First_Sem > 85 AND Department_Id = (SELECT Department_Id FROM Department WHERE Department_Name = 'Computer Engineering');
In the above query, we are modifying the student information of those students where the first-semester percentage is greater than 85, and the department name is 'Computer Engineering’.
First, SELECT Department_Id FROM Department WHERE Department_Name = 'Computer Engineering'; gets executed, and the output of this inner query is
Department_Id |
1 |
As the inner query output is calculated, the output is represented as input for the main query. The main query is executed as UPDATE D_Students SET Department_Id = 6 WHERE First_Sem > 85 AND Department_Id = 1;
We will execute the SELECT statement to check whether the data is modified or not.
SELECT * FROM D_Students;
The output of the above query is as follows:
Student_Id | Student_Name | First_Sem | Second_Sem | Third_Sem | Fourth_Sem | Fifth_Sem | Sixth_Sem | Total | Department_Id |
202111 | Vaishnavi Patil | 94 | 91 | 88 | 85 | 95 | 92 | 91 | 6 |
202112 | Vaibhav Lokhande | 85 | 90 | 92 | 80 | 85 | 82 | 86 | 2 |
202113 | Yash Dhull | 90 | 88 | 94 | 87 | 85 | 90 | 89 | 3 |
202114 | Sonali Patole | 95 | 90 | 92 | 88 | 92 | 90 | 91 | 4 |
202115 | Axar Patel | 85 | 80 | 82 | 86 | 92 | 84 | 85 | 1 |
202116 | Meena Mishra | 78 | 75 | 80 | 74 | 85 | 77 | 78 | 3 |
202117 | Mahesh Kumbhar | 75 | 80 | 75 | 78 | 80 | 76 | 77 | 5 |
202118 | Sakshi Patil | 80 | 78 | 74 | 78 | 80 | 77 | 78 | 2 |
202119 | Sopan Bhore | 70 | 68 | 75 | 75 | 80 | 80 | 75 | 2 |
202220 | Prajwal Lokhande | 80 | 85 | 85 | 75 | 78 | 80 | 81 | 4 |
202221 | Anuja Wanare | 85 | 88 | 86 | 82 | 84 | 85 | 85 | 5 |
202222 | Venkatesh Iyer | 90 | 89 | 87 | 90 | 92 | 91 | 90 | 3 |
202223 | Anushka Sen | 70 | 75 | 71 | 74 | 80 | 78 | 75 | 1 |
202224 | Aakash Jain | 80 | 75 | 72 | 74 | 85 | 80 | 78 | 4 |
202225 | Akshay Agarwal | 85 | 80 | 78 | 88 | 90 | 82 | 84 | 5 |
202226 | Shwetali Bhagwat | 90 | 80 | 85 | 88 | 90 | 80 | 86 | 6 |
202227 | Mayuri Wagh | 80 | 80 | 85 | 80 | 82 | 85 | 82 | 4 |
202228 | Utkarsh Rokade | 85 | 80 | 80 | 90 | 84 | 84 | 84 | 5 |
202229 | Manthan Koli | 85 | 75 | 84 | 78 | 82 | 80 | 81 | 2 |
202230 | Mayur Jain | 80 | 88 | 87 | 90 | 92 | 90 | 88 | 1 |

Example 2: Execute a query to modify the student's information where the student name starts with the letter 'A' and department names are 'Automobile Engineering' and 'Civil Engineering'.
UPDATE D_Students SET Department_Id = '7' WHERE Student_Name LIKE 'A%' AND Department_Id IN (SELECT Department_Id FROM Department WHERE Department_Name IN ('Automobile Engineering','Civil Engineering'));
In the above query, we modify those students' information where student_name starts with the letter 'A' and department names are 'Automobile Engineering' and 'Civil Engineering'. The query execution is the same as explained in the above example.
We will execute the SELECT statement to check whether the data is modified or not.
SELECT * FROM D_Students;
The output of the above query is as follows:
Student_Id | Student_Name | First_Sem | Second_Sem | Third_Sem | Fourth_Sem | Fifth_Sem | Sixth_Sem | Total | Department_Id |
202111 | Vaishnavi Patil | 94 | 91 | 88 | 85 | 95 | 92 | 91 | 6 |
202112 | Vaibhav Lokhande | 85 | 90 | 92 | 80 | 85 | 82 | 86 | 2 |
202113 | Yash Dhull | 90 | 88 | 94 | 87 | 85 | 90 | 89 | 3 |
202114 | Sonali Patole | 95 | 90 | 92 | 88 | 92 | 90 | 91 | 4 |
202115 | Axar Patel | 85 | 80 | 82 | 86 | 92 | 84 | 85 | 1 |
202116 | Meena Mishra | 78 | 75 | 80 | 74 | 85 | 77 | 78 | 3 |
202117 | Mahesh Kumbhar | 75 | 80 | 75 | 78 | 80 | 76 | 77 | 5 |
202118 | Sakshi Patil | 80 | 78 | 74 | 78 | 80 | 77 | 78 | 2 |
202119 | Sopan Bhore | 70 | 68 | 75 | 75 | 80 | 80 | 75 | 2 |
202220 | Prajwal Lokhande | 80 | 85 | 85 | 75 | 78 | 80 | 81 | 4 |
202221 | Anuja Wanare | 85 | 88 | 86 | 82 | 84 | 85 | 85 | 7 |
202222 | Venkatesh Iyer | 90 | 89 | 87 | 90 | 92 | 91 | 90 | 3 |
202223 | Anushka Sen | 70 | 75 | 71 | 74 | 80 | 78 | 75 | 1 |
202224 | Aakash Jain | 80 | 75 | 72 | 74 | 85 | 80 | 78 | 7 |
202225 | Akshay Agarwal | 85 | 80 | 78 | 88 | 90 | 82 | 84 | 7 |
202226 | Shwetali Bhagwat | 90 | 80 | 85 | 88 | 90 | 80 | 86 | 6 |
202227 | Mayuri Wagh | 80 | 80 | 85 | 80 | 82 | 85 | 82 | 4 |
202228 | Utkarsh Rokade | 85 | 80 | 80 | 90 | 84 | 84 | 84 | 5 |
202229 | Manthan Koli | 85 | 75 | 84 | 78 | 82 | 80 | 81 | 2 |
202230 | Mayur Jain | 80 | 88 | 87 | 90 | 92 | 90 | 88 | 1 |

3. Subquery with the DELETE Statement
The DELETE statement is used with the subqueries to remove data from the table.
The Syntax of the Subquery with the DELETE statement is as follows:
DELETE FROM Table_Name WHERE Column_Name Conditions operator (SELECT * FROM Table_Name WHERE Column_Name Conditions);
Example: Execute a query to remove the student's information where department names are 'Civil Engineering’, ‘Electrical Engineering’, and ‘Electronics and Telecommunication Engineering’.
DELETE FROM D_Students WHERE Department_Id IN (SELECT Department_Id FROM Department WHERE Department_Name IN ('Civil Engineering', 'Electrical Engineering', 'Electronics and Telecommunication Engineering'));
In the above query, we removed the student's information from the D_Students table where department names are 'Civil Engineering’, ‘Electrical Engineering’, and ‘Electronics and Telecommunication Engineering’.
First, SELECT Department_Id FROM Department WHERE Department_Name IN ('Civil Engineering', 'Electrical Engineering', 'Electronics and Telecommunication Engineering'; gets executed, and the output of this inner query is
Department_Id |
5 |
6 |
7 |
As the inner query output is calculated, the output is represented as input for the main query. The main query is executed as DELETE FROM D_Students WHERE Department_Id IN (5, 6, 7);
The main query removes the student’s information where department id is 5, 6, and 7 from the D_Students table.
We will execute the SELECT statement to check whether the data is removed or not.
SELECT * FROM D_Students;
The output of the above query is as follows:
Student_Id | Student_Name | First_Sem | Second_Sem | Third_Sem | Fourth_Sem | Fifth_Sem | Sixth_Sem | Total | Department_Id |
202112 | Vaibhav Lokhande | 85 | 90 | 92 | 80 | 85 | 82 | 86 | 2 |
202113 | Yash Dhull | 90 | 88 | 94 | 87 | 85 | 90 | 89 | 3 |
202114 | Sonali Patole | 95 | 90 | 92 | 88 | 92 | 90 | 91 | 4 |
202115 | Axar Patel | 85 | 80 | 82 | 86 | 92 | 84 | 85 | 1 |
202116 | Meena Mishra | 78 | 75 | 80 | 74 | 85 | 77 | 78 | 3 |
202118 | Sakshi Patil | 80 | 78 | 74 | 78 | 80 | 77 | 78 | 2 |
202119 | Sopan Bhore | 70 | 68 | 75 | 75 | 80 | 80 | 75 | 2 |
202220 | Prajwal Lokhande | 80 | 85 | 85 | 75 | 78 | 80 | 81 | 4 |
202222 | Venkatesh Iyer | 90 | 89 | 87 | 90 | 92 | 91 | 90 | 3 |
202223 | Anushka Sen | 70 | 75 | 71 | 74 | 80 | 78 | 75 | 1 |
202227 | Mayuri Wagh | 80 | 80 | 85 | 80 | 82 | 85 | 82 | 4 |
202229 | Manthan Koli | 85 | 75 | 84 | 78 | 82 | 80 | 81 | 2 |
202230 | Mayur Jain | 80 | 88 | 87 | 90 | 92 | 90 | 88 | 1 |
