Nesting Queries

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 EMPLOYEE table.

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 (SELECT-\verbFROM"-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]
     FROM     table-name
     WHERE    column-name IN
       (SELECT   column-name
        FROM     table-name
        WHERE    search-condition) ;

Syntax Notes:

• The first SELECT-\verbFROM"-WHERE block is the outer query.

• The second SELECT-\verbFROM"-WHERE block in parentheses is the subquery.

• The IN operator is normally used if the inner query returns many rows and one column.