SQL Sub Query

A Subquery is a query within another SQL query and embedded within the WHERE clause.

Important Rule:

• A subquery can be placed in a number of SQL clauses like WHERE clause, FROM clause, HAVING clause.
• You can use Subquery with SELECT, UPDATE, INSERT, DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.
• A subquery is a query within another query. The outer query is known as the main query, and the inner query is known as a subquery.
• Subqueries are on the right side of the comparison operator.
• A subquery is enclosed in parentheses.
• In the Subquery, ORDER BY command cannot be used. But GROUP BY command can be used to perform the same function as ORDER BY command.

1. Subqueries with the Select Statement

SQL subqueries are most frequently used with the Select statement.

Syntax

Example

Consider the EMPLOYEE table have the following records:

The subquery with a SELECT statement will be:

This would produce the following result:

2. Subqueries with the INSERT Statement

• SQL subquery can also be used with the Insert statement. In the insert statement, data returned from the subquery is used to insert into another table.
• In the subquery, the selected data can be modified with any of the character, date functions.

Syntax:

Example

Consider a table EMPLOYEE_BKP with similar as EMPLOYEE.

Now use the following syntax to copy the complete EMPLOYEE table into the EMPLOYEE_BKP table.

3. Subqueries with the UPDATE Statement

The subquery of SQL can be used in conjunction with the Update statement. When a subquery is used with the Update statement, then either single or multiple columns in a table can be updated.

Syntax

Example

Let's assume we have an EMPLOYEE_BKP table available which is backup of EMPLOYEE table. The given example updates the SALARY by .25 times in the EMPLOYEE table for all employee whose AGE is greater than or equal to 29.

This would impact three rows, and finally, the EMPLOYEE table would have the following records.

4. Subqueries with the DELETE Statement

The subquery of SQL can be used in conjunction with the Delete statement just like any other statements mentioned above.

Syntax

Example

Let's assume we have an EMPLOYEE_BKP table available which is backup of EMPLOYEE table. The given example deletes the records from the EMPLOYEE table for all EMPLOYEE whose AGE is greater than or equal to 29.

This would impact three rows, and finally, the EMPLOYEE table would have the following records.