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:

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:

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.

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); 

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.