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:

1. We can use SQL Sub-query along with SELECT Statement, UPDATE Statement, INSERT Statement and DELETE Statement in the SQL statement.
2. 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.
3. A comparison operator can also be used.
4. The inner query is closed within the parenthesis, and the inner query is placed on the right side of the comparison operator.
5. You cannot use the ORDER BY clause in the Sub-query but can use the GROUP BY clause.
6. 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