Subqueries

A  Sub-query  is  a  query  which  can  hold  multiple  query  statements  each  nested  within another. This Sub-query is used to retrieve selected data from tables which depend on the values in the table. It is also known as as a nested query. The Statement containing a subquery is known as the parent statement and the query inside is known as the child query. A parent statement uses the rows returned through the child query. The Subqueries can be used in the following conditions:

•To describe the set of rows to be inserted into the target table of a INSERT or CREATE TABLE statement.
•To describe one or more values to be assigned to existing rows of an UPDATE statement.
Let consider a case where the details of an employee who earns the maximum salary must be displayed.  In that type of situations, first the maximum salary must be calculated.  After that the maximum salary must be compared with values in the emp table and their details must be displayed. These two queries can be combined into a single query known as sub-query.

Example

SELECT ename,deptno,sal FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);
In the given query, first the maximum salary is computed and the result is compared with the parent query for the output.
The output will display like:

ENAME    DEPTNO     SAL
KING        10        5000

Within the employee table the record with the employee name KING has the maximum salary of 5000.

Example

SELECT PCODE, PNAME FROM PRODUCT WHERE PCODE=
(SELECT PCODE FROM ORDPRD WHERE QTY=
(SELECT MAX(QTY) FROM ORDPRD))

The output will be:

PCODE    PNAME
106        0.75 ton a/c
The above example displays the product code and the product name from the product table for that the ordered quantity is the maximum.