When you write a SELECT statement, you can place another SELECT statement within the WHERE clause. Each additional SELECT starts a subquery.
A subquery can, in turn, include another subquery whose value is substituted into its WHERE clause. In addition, a WHERE clause can include subqueries in more than one search condition. The subquery can refer to tables and columns that are different than the ones used in the main query.
The following statement selects the division and location from the ORG table of the employee whose ID in the STAFF table is 280:
SELECT DIVISION, LOCATION
FROM ORG
WHERE DEPTNUMB = (SELECT DEPT
FROM STAFF
WHERE ID = 280)
When processing this statement, DB2 first determines the result of the subquery. The result is 66, since the employee with ID 280 is in department 66. Then the final result is taken from the row of the ORG table whose DEPTNUMB column has the value of 66. The final result is:
DIVISION LOCATION
---------- -------------
Western San Francisco
When you use a subquery, the database manager evaluates it and substitutes the resulting value directly into the WHERE clause.
Subqueries are further discussed Correlated Subqueries.