At times you may wish to retrieve rows in one table based on
conditions in a related table. For example, suppose Personnel needed
to call in any employees in the Information Division receiving only
partial benefits, to discuss options for upgrading to the full benefit
program. To answer this query, you have to retrieve the names of all
departments in the Information Division, found in the DEPARTMENT
table, and then the employees with partial benefits in the Information
Division departments, found in the
In other situations, you may want to formulate a query from one table that required you to make two passes through the table in order to obtain the desired results. For example, you may want to retrieve a list of staff members earning a salary higher than Jackson, but you don't know Jackson's salary. To answer this query, you first find Jackson's salary; then you compare the salary of each staff member to his.
One approach is to develop a subquery, which involves embedding
a query (
WHERE block) within the
WHERE clause of another
query. This is sometimes referred to as a "nested query".
The format of a nested query is:
SELECT column-name1 [,column-name2]
WHERE column-name IN
WHERE search-condition) ;
WHEREblock is the outer query.
WHEREblock in parentheses is the subquery.