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

1. SELECT column_name  
2. FROM table_name  
3. WHERE column_name expression operator   
4. ( 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:

1. SELECT *   
2.     FROM EMPLOYEE  
3.     WHERE ID IN (SELECT ID   
4.     FROM EMPLOYEE   
5.     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:

1. INSERT INTO table_name (column1, column2, column3....)   
2. SELECT *  
3. FROM table_name  
4. 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.

1. INSERT INTO EMPLOYEE_BKP  
2.    SELECT * FROM EMPLOYEE   
3.    WHERE ID IN (SELECT ID   
4.    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

1. UPDATE table  
2. SET column_name = new_value  
3. WHERE VALUE OPERATOR  
4.    (SELECT COLUMN_NAME  
5.    FROM TABLE_NAME  
6.    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.

1. UPDATE EMPLOYEE  
2.    SET SALARY = SALARY * 0.25  
3.    WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP  
4.       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

1. DELETE FROM TABLE_NAME  
2. WHERE VALUE OPERATOR  
3.    (SELECT COLUMN_NAME  
4.    FROM TABLE_NAME  
5.    WHERE condition);   

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.

1. DELETE FROM EMPLOYEE  
2.    WHERE AGE IN (SELECT AGE FROM EMPLOYEE_BKP  
3.       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