Here are some points concerning the use of nested queries:
WHEREblocks. The portion in parentheses is called the subquery. The subquery is evaluated first; then the outer query is evaluated based on the result of the subquery. In effect, the nested query can be looked at as being equivalent to:
SELECT column-name1 [,column-name2] ... FROM table-name WHERE column-name IN (set of values from the subquery) ;where the set of values is determined from the inner
=, etc., can be used to link an outer query to a subquery when the subquery returns a single value.
SELECTclause, so that the resulting set of values can be passed back to the next outer query for evaluation.
>, etc.) can be used to link the subquery to the next higher level.
Example: List the names of all personnel in the Information Division by entering this statement:
WHERE DEPT IN
WHERE DIV = 'INFO') ;
Parentheses are placed around the subquery, as shown below the outer
The results are:
To understand how this expression retrieves its results, work from the
bottom up in evaluating the
SELECT statement. In other words, the
subquery is evaluated first. This results in a set of values that can
be used as the basis for the outer query. The innermost
retrieves the following set of department codes, as departments
in the Information (`INFO') Division: ISM, LIB, SPI.
In the outermost
SELECT block, the IN operator tests whether any
department code in the
EMPLOYEE table is contained in the set of
department codes values retrieved from the inner
SELECT block; i.e.,
ISM, LIB, or SPI.
In effect, the outer
SELECT block is equivalent to:
WHERE DEPT IN ('ISM', 'LIB', 'SPI') ;
where the values in parentheses are values from the subquery.
Thus, the employee names Chapman, Dedrich, Krinski and Peters are retrieved.
Subqueries can be nested several levels deep within a query, as the next example illustrates.
Example: Acme Industrial's ink sales are up, and management wishes to reward everyone in the division(s) most responsible. List the names of all employees in any division whose personnel have contributed reports on ink to the corporate library, along with their department and benefit level.
Use this statement:
SELECT ENAME, DEPT, BENEFITS
WHERE DEPT IN
WHERE DIV IN
WHERE DEPT IN
WHERE TITLE LIKE 'ink') ) ) ;
IN is used for each subquery since in each case it is possible to
retrieve several values. You could use `
=' instead where you
knew only one value would be retrieved; e.g. where you wanted only the
division with the greatest number of reports rather than all divisions
Results of the above nested query are:
ENAME DEPT BENEFITS
Aster, John A. MKT FULL
Jackson, Herbert RND FULL
Sanchez, Carla MKT FULL
Smith, Roberta MKT PART
Jones, David RND FULL
Washington, G. MFG FULL
Ferrer, Miguel CSS FULL
Brown, Penelope MKT FULL
Again, remember that a nested query is evaluated from the bottom up;
i.e., from the innermost query to the outermost query. First, a
text search is done (TITLE LIKE 'INK') of report titles from the
REPORT table. Two such titles are located: "Disappearing Ink" by
Herbert Jackson from Research and Development (RND), and "Ink
Promotional Campaign" by Carla Sanchez from Product Marketing and
Sales (MKT). Thus the results of the innermost query produces a list
of two department codes: RND and MKT.
Once the departments are known, a search is done of the DEPARTMENT table, to locate the division or divisions to which these departments belong. Both departments belong to the Product Division (PROD); thus the results of the next subquery produces one item: PROD.
A second pass is made through the same table, DEPARTMENT, to find all departments which belong to the Product Division. This search produces a list of four Product Division departments: MKT, RND, MFG, and CSS, adding Manufacturing as well as Customer Support and Service to the list.
This list is passed to the outermost query so that the
may be searched for all employees in those departments. The final
listing is retrieved, as above.
Here is another example specifically designed to illustrate the use of a subquery making two passes through the same table to find the desired results.
Example: List the names of employees who have salaries greater than that of Herbert Jackson. Assume you do not know Jackson's salary.
Enter this statement:
SELECT ENAME, SALARY
WHERE SALARY >
WHERE ENAME = 'Jackson, Herbert') ;
The compare operator
> can be used (as could
= and other
compare operators) where a single value only will be returned from the
Using the sample information in our
EMPLOYEE table, the results are as
Aster, John A. 32000
Barrington, Kyle 45000
Price Stella 42000
Sanchez, Carla 35000
The subquery searches the
EMPLOYEE table and returns the value
30000, the salary listed for Herbert Jackson. Then the outer
SELECT block searches the
EMPLOYEE table again to retrieve all
SALARY > 30000. Thus the above employees with
higher salaries are retrieved.