Nested Queries

A nested query, also known as a subquery or subselect, is a SELECT query embedded within the WHERE or HAVING clause of another SQL query. The data returned by the subquery is used by the outer statement in the same way a literal value would be used.

Nested queries provide an easy and efficient way to handle the queries that depend on the results from another query. They are almost identical to the normal SELECT statements, but there are few restrictions. The most important ones are listed below:

1.          A subquery must always appear within parentheses.
2.        A subquery must return only one column. This means you cannot use SELECT * in a subquery unless the table you are referring has only one column. Subquery may be used that returns multiple columns, if the purpose is row comparison.
3.       Subqueries can be used that return more than one row with multiple value operators, such as the IN or NOT IN operator.
4.      A subquery cannot be a UNION. Only a single SELECT statement is allowed.

A subquery can be nested inside the WHERE or HAVING clause of an outer SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery.

Subqueries with the SELECT Statement

Subqueries are most frequently used with the SELECT statement. The basic syntax is as follows

SELECT column_name [, column_name ] 

FROM  table1 [, table2 ]

WHERE column_name OPERATOR 

(SELECT column_name [, column_name ]  FROM table1 [, table2 ]  [WHERE]);

Example:

SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 4500) ;

Subqueries with the INSERT Statement

The INSERT statement uses the data returned from the subquery to insert into another table.

The basic syntax is as follows:

INSERT INTO table_name [ (column1 [, column2 ])]

   SELECT [ * |  column1 [, column2 ]

   FROM table1 [, table2 ]

   [ WHERE VALUE OPERATOR ];

Example:

INSERT INTO CUSTOMERS_BKP (SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS) ;

Subqueries with the UPDATE Statement

The basic syntax is as follows:

UPDATE table SET column_name = new_value

[ WHERE OPERATOR [ VALUE ]

   (SELECT COLUMN_NAME FROM TABLE_NAME)  [ WHERE) ]

Example:

UPDATE CUSTOMERS SET SALARY = SALARY * 0.25 WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >= 27 );

Subqueries with the DELETE Statement

The basic syntax is as follows:

DELETE FROM TABLE_NAME [ WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME FROM TABLE_NAME) [ WHERE) ]

Example:

DELETE FROM CUSTOMERS WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP   

WHERE AGE >= 27);