Command Discussion

Here are some points concerning the use of nested queries:

The above statement contains two SELECT-FROM-WHERE blocks. 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 SELECT-FROM-WHERE block.
The IN operator is used to link the outer query to the subquery when the subquery returns a set of values (one or more). Other comparison operators, such as <, >, =, etc., can be used to link an outer query to a subquery when the subquery returns a single value.
The subquery must have only a single column or expression in the SELECT clause, so that the resulting set of values can be passed back to the next outer query for evaluation.
You are not limited to one subquery. Though it isn't advised, there could be as many as 16 levels of subqueries, with no fixed limitation except limits of memory and disk-space on the machine in use. Any of the operators (IN, =, <, >, 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:

SELECT   ENAME
     FROM     EMPLOYEE
     WHERE    DEPT IN
       (SELECT   DEPT
        FROM     DEPARTMENT
        WHERE    DIV = 'INFO') ;

Parentheses are placed around the subquery, as shown below the outer WHERE clause.

The results are:

ENAME
  Chapman, Margaret
  Dedrich, Franz
  Krinski, Wanda
  Peters, Robert

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 SELECT block 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:

SELECT   ENAME
     FROM     EMPLOYEE
     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
     FROM     EMPLOYEE
     WHERE    DEPT IN
       (SELECT   DEPT
        FROM     DEPARTMENT
        WHERE    DIV IN
          (SELECT   DIV
           FROM     DEPARTMENT
           WHERE    DEPT IN
             (SELECT   DEPT
              FROM     REPORT
              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 contributing reports.

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 EMPLOYEE table 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
     FROM     EMPLOYEE
     WHERE    SALARY >
       (SELECT   SALARY
        FROM     EMPLOYEE
        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 subquery.

Using the sample information in our EMPLOYEE table, the results are as follows:

ENAME              SALARY
  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 employees with SALARY > 30000. Thus the above employees with higher salaries are retrieved.