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
SELECT column_name
FROM table_name
WHERE column_name expression operator
( SELECT column_name from table_name WHERE ... );
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:
SELECT *
FROM EMPLOYEE
WHERE ID IN (SELECT
ID FROM EMPLOYEE
WHERE SALARY > 4500);
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:
INSERT INTO table_name (column1, column2, column3.... )
SELECT *
FROM table_name
WHERE VALUE
OPERATOR
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.
INSERT INTO EMPLOYEE_BKP
SELECT * FROM EMPLOYEE
WHERE ID IN (SELECT ID
FROM EMPLOYEE);
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
UPDATE table
SET column_name = new_value
WHERE VALUE OPERATOR
(SELECT COLUMN_NAME
FROM TABLE_NAME
WHERE condition);
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.
UPDATE EMPLOYEE
SET SALARY = SALARY * 0.25
WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
WHERE AGE >= 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
DELETE FROM TABLE_NAME
WHERE VALUE OPERATOR
(SELECT COLUMN_NAME
FROM TABLE_NAME
WHERE condition);
⇧
https://www.javatpoint.com/dbms-sql-sub-queries 3/
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.
DELETE FROM EMPLOYEE
WHERE AGE IN (SELECT AGE FROM EMPLOYEE_BKP
WHERE AGE >= 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
https://www.javatpoint.com/dbms-sql-sub-queries 4/
