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:

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.

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

Output:

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.

Output:

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:

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.

Table: Old_Employee

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:

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

Output:

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:

Table: New_Employee

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

Table: Old_Employee

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:

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

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:

Table: Employee_Details

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

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

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

Output:

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

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:

Table: Employee_Details

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

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

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

Output:

Table: Employee_Details