Javatpoint Logo
Javatpoint Logo

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:

ID NAME AGE ADDRESS SALARY
1 John 20 US 2000.00
2 Stephan 26 Dubai 1500.00
3 David 27 Bangkok 2000.00
4 Alina 29 UK 6500.00
5 Kathrin 34 Bangalore 8500.00
6 Harry 42 China 4500.00
7 Jackson 25 Mizoram 10000.00

The subquery with a SELECT statement will be:

This would produce the following result:

ID NAME AGE ADDRESS SALARY
4 Alina 29 UK 6500.00
5 Kathrin 34 Bangalore 8500.00
7 Jackson 25 Mizoram 10000.00

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.

ID NAME AGE ADDRESS SALARY
1 John 20 US 2000.00
2 Stephan 26 Dubai 1500.00
3 David 27 Bangkok 2000.00
4 Alina 29 UK 1625.00
5 Kathrin 34 Bangalore 2125.00
6 Harry 42 China 1125.00
7 Jackson 25 Mizoram 10000.00

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.

ID NAME AGE ADDRESS SALARY
1 John 20 US 2000.00
2 Stephan 26 Dubai 1500.00
3 David 27 Bangkok 2000.00
7 Jackson 25 Mizoram 10000.00

Next TopicDBMS SQL Clauses




Please Share

facebook twitter google plus pinterest

Learn Latest Tutorials


B.Tech / MCA