SQL Subquery

The Subquery or Inner query is an SQL query placed inside another SQL query. It is embedded in the HAVING or WHERE clause of the SQL statements.

Following are the important rules which must be followed by the SQL Subquery:

1. The SQL subqueries can be used with the following statements along with the SQL expression operators:

SELECT statement,
UPDATE statement,
INSERT statement, and
DELETE statement.

2. The subqueries in SQL are always enclosed in the parenthesis and placed on the right side of the SQL operators.

3. We cannot use the ORDER BY clause in the subquery. But, we can use the GROUP BY clause, which performs the same function as the ORDER BY clause.

4. If the subquery returns more than one record, we have to use the multiple value operators before the Subquery.

5. We can use the BETWEEN operator within the subquery but not with the subquery.

Subquery with SELECT statement

In SQL, inner queries or nested queries are used most frequently with the SELECT statement. The syntax of Subquery with the SELECT statement is described in the following block:

SELECT Column_Name1, Column_Name2, ...., Column_NameN
FROM Table_Name WHERE Column_Name Comparison_Operator
( SELECT Column_Name1, Column_Name2, ...., Column_NameN
FROM Table_Name WHERE condition;

Examples of Subquery with the SELECT Statement

Example 1: This example uses the Greater than comparison operator with the Subquery.

Let's take the following table named Student_Details, which contains Student_RollNo., Stu_Name, Stu_Marks, and Stu_City column.

Student_RollNo.	Stu_Name	Stu_Marks	Stu_City
1001	Akhil	85	Agra
1002	Balram	78	Delhi
1003	Bheem	87	Gurgaon
1004	Chetan	95	Noida
1005	Diksha	99	Agra
1006	Raman	90	Ghaziabad
1007	Sheetal	68	Delhi

The following SQL query returns the record of those students whose marks are greater than the average of total marks:

SELECT * FROM Student_Details WHERE Stu_Marks> ( SELECT AVG(Stu_Marks ) FROM Student_Details);

Output:

Student_RollNo.	Stu_Name	Stu_Marks	Stu_City
1003	Bheem	87	Gurgaon
1004	Chetan	95	Noida
1005	Diksha	99	Agra
1006	Raman	90	Ghaziabad

Example 2: This example uses the IN operator with the subquery.

Let's take the following two tables named Faculty_Details and Department tables. The Faculty_Details table contains ID, Name, Dept_ID, and address of faculties. And, the Department table contains the Dept_ID, Faculty_ID, and Dept_Name.

Faculty_ID	Name	Dept_ID	Address
101	Bheem	1	Gurgaon
102	Chetan	2	Noida
103	Diksha	NULL	Agra
104	Raman	4	Ghaziabad
105	Yatin	3	Noida
106	Anuj	NULL	Agra
107	Rakes	5	Gurgaon

Dept_ID	Faculty_ID	Dept_Name
1	101	BCA
2	102	B.Tech
3	105	BBA
4	104	MBA
5	107	MCA

SELECT * FROM Department WHERE Faculty_ID IN (
SELECT Faculty_ID FROM Faculty WHERE City = 'Noida' OR City = 'Gurgaon' ) ;

Output:

Dept_ID	Faculty_ID	Dept_Name
1	101	BCA
2	102	B.Tech
3	105	BBA
5	107	MCA

Subquery with the INSERT statement

We can also use the subqueries and nested queries with the INSERT statement in Structured Query Language. We can insert the results of the subquery into the table of the outer query. The syntax of Subquery with the INSERT statement is described in the following block:

INSERT INTO Table_Name SELECT * FROM Table_Name WHERE Column_Name Operator (Subquery);

Examples of Subquery with the INSERT Statement

Example1: This example inserts the record of one table into another table using subquery with WHERE clause.

Let's take Old_Employee and New_Employee tables. The Old_Employee and New_Employee table contain the same number of columns. But, both the tables contain different records.

Emp_ID	Emp_Name	Emp_Salary	Address
1001	Akhil	50000	Agra
1002	Balram	25000	Delhi
1003	Bheem	45000	Gurgaon
1004	Chetan	60000	Noida
1005	Diksha	30000	Agra
1006	Raman	50000	Ghaziabad
1007	Sheetal	35000	Delhi

Table: Old_Employee

Emp_ID	Emp_Name	Emp_Salary	Address
1008	Sumit	50000	Agra
1009	Akash	55000	Delhi
1010	Devansh	65000	Gurgaon

Table: New_Employee

The New_Employee contains the details of new employees. If you want to move the details of those employees whose salary is greater than 40000 from the Old_Employee table to the New_Employee table. Then for this issue, you have to type the following query in SQL:

INSERT INTO New_Employee SELECT * FROM Old_Employee WHERE Emp_Salary > 40000;

Now, you can check the details of the updated New_Employee table by using the following SELECT query:

SELECT * FROM New_Employee;

Output:

Emp_ID	Emp_Name	Emp_Salary	Address
1008	Sumit	50000	Agra
1009	Akash	55000	Delhi
1010	Devansh	65000	Gurgaon
1001	Akhil	50000	Agra
1003	Bheem	45000	Gurgaon
1004	Chetan	60000	Noida
1006	Raman	50000	Ghaziabad

Table: New_Employee

Example 2: This example describes how to use ANY operator with subquery in the INSERT Statement.

Here we have taken the New_Employee, old_Employee, and Department table.

The data of the New_Employee table is shown in the following table:

Emp_ID	Emp_Name	Emp_Salary	Dept_ID
1008	Sumit	50000	401

Table: New_Employee

The data of the old_Employee table is shown in the below table:

Emp_ID	Emp_Name	Emp_Salary	Dept_ID
1001	Akhil	50000	404
1002	Balram	25000	403
1003	Bheem	45000	405
1004	Chetan	60000	402
1005	Ram	65000	407
1006	Shyam	55500	NULL
1007	Shobhit	60000	NULL

Table: Old_Employee

The data of Department table is shown in the below table:

Dept_ID	Dept_Name	Emp_ID
401	Administration	1008
402	HR	1004
403	Testing	1002
404	Coding	1001
405	Sales	1003
406	Marketing	NULL
407	Accounting	1005

INSERT INTO New_Employee
SELECT * FROM Old_Employee
WHERE Emp_ID = ANY( SELECT Emp_ID FROM Department WHERE Dept_ID = 407 OR Dept_ID = 406 );

Now, check the details of the New_Employee table by using the following SELECT statement:

Output:

Emp_ID	Emp_Name	Emp_Salary	Dept_ID
1008	Sumit	50000	401
1005	Ram	65000	407

Subquery with the UPDATE statement

The subqueries and nested queries can be used with the UPDATE statement in Structured Query Language for updating the columns of the existing table. We can easily update one or more columns using a subquery with the UPDATE statement.

Syntax of Subquery with the UPDATE statement

UPDATE Table_Name SET Column_Name = New_value WHERE Value OPERATOR (SELECT COLUMN_NAME FROM TABLE_NAME WHERE Condition) ;

Example of Subquery with the UPDATE statement

This example updates the record of one table using the IN operator with Subquery in the UPDATE statement.

Let's take an Employee_Details and Department table.

The data of the Employee_Details table is shown in the following table:

Emp_ID	Emp_Name	Emp_Salary	Dept_ID
1001	Akhil	50000	404
1002	Balram	25000	403
1003	Bheem	45000	405
1004	Chetan	60000	402
1005	Ram	65000	407
1006	Shyam	55500	NULL
1007	Shobhit	60000	NULL

Table: Employee_Details

The data of Department table is shown in the below table:

Dept_ID	Dept_Name	Emp_ID	Dept_Grade
401	Administration	1008	B
402	HR	1004	A
403	Testing	1002	A
404	Coding	1001	B
405	Sales	1003	A
406	Marketing	NULL	C
407	Accounting	1005	A

The following updates the salary of those employees whose Department Grade is A:

UPDATE Employee_Details SET Emp_SalaryEmp_Salary = Emp_Salary + 5000 WHERE Emp_ID IN ( SELECT Emp_ID FROM Department WHERE Dept_Grade = 'A' ) ;

The following query will show the updated data of the Employee_Details table in the output:

SELECT * FROM Employee_Details ;

Output:

Emp_ID	Emp_Name	Emp_Salary	Dept_ID
1001	Akhil	50000	404
1002	Balram	30000	403
1003	Bheem	50000	405
1004	Chetan	65000	402
1005	Ram	70000	407
1006	Shyam	55500	NULL
1007	Shobhit	60000	NULL

Table: Employee_Details

Subquery with the DELETE statement

We can easily delete one or more records from the SQL table using Subquery with the DELETE statement in Structured Query Language.

Syntax of Subquery with DELETE statement

DELETE FROM Table_Name WHERE Value OPERATOR (SELECT COLUMN_NAME FROM TABLE_NAME WHERE Condition) ;

Example of Subquery with DELETE statement

This example deletes the records from the table using the IN operator with Subquery in the DELETE statement.

Let's take an Employee_Details and Department table.

The data of the Employee_Details table is shown in the following table:

Emp_ID	Emp_Name	Emp_Salary	Dept_ID
1001	Akhil	50000	404
1002	Balram	25000	403
1003	Bheem	45000	405
1004	Chetan	60000	402
1005	Ram	65000	407
1006	Shyam	55500	NULL
1007	Shobhit	60000	NULL
1008	Ankit	48000	401

Table: Employee_Details

The data of Department table is shown in the below table:

Dept_ID	Dept_Name	Emp_ID	Dept_Grade
401	Administration	1008	C
402	HR	1004	A
403	Testing	1002	C
404	Coding	1001	B
405	Sales	1003	A
406	Marketing	NULL	C
407	Accounting	1005	C

The following query deletes the record of those employees from the Employee_Details whose Department Grade is C:

DELETE FROM Employee_Details WHERE Emp_ID IN ( SELECT Emp_ID FROM Department WHERE Dept_Grade = 'C' ) ;

The following query will show the updated data of the Employee_Details table in the output:

SELECT * FROM Employee_Details ;

Output:

Emp_ID	Emp_Name	Emp_Salary	Dept_ID
1001	Akhil	50000	404
1003	Bheem	45000	405
1004	Chetan	60000	402
1006	Shyam	55500	NULL
1007	Shobhit	60000	NULL

Table: Employee_Details

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection

SQL String Functions

Miscl

SQL MCQ

PL/SQL Tutorial

Sql Interview Question

SQl Quiz

SQL Subquery

Subquery with SELECT statement

Subquery with the INSERT statement

Examples of Subquery with the INSERT Statement

Subquery with the UPDATE statement

Subquery with the DELETE statement

Comment / Reply From

You May Also Like

Popular Post

Language

Newsletter