When you write an SQL SELECT statement, you can place additional SELECT statements within the WHERE clause. Each additional SELECT starts a subquery.
A subquery can then, in turn, include another separate subquery, whose result is substituted into the original subquery's 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 a statement, DB2 first determines the result of the subquery. The result from this example's subquery is 66, since the employee with ID 280 is in department 66. The final result is then 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 in Correlated Subqueries.