Subquery Using EXISTS

There may be situations in which you are interested in retrieving records where there exists at least one row that satisfies a particular condition. For example, the resume records stored in the RESUME table may include some individuals who are already employed at Acme Industrial and so are entered in the EMPLOYEE table. If you wanted to know which employees were seeking new jobs at the present time, an existence test using the keyword EXISTS can be used to answer such a query.

This type of query is developed with a subquery. The WHERE clause of the outer query is used to test the existence of rows that result from a subquery. The form of the WHERE clause that is linked to the subquery is:

WHERE [NOT] EXISTS (subquery)

This clause is satisfied if there is at least one row that would be returned by the subquery. If so, the subquery does not return any values; it just sets an indicator value to true. On the other hand, if no elements satisfy the condition, or the set is empty, the indicator value is false.

The subquery should return a single column only.

Example: Retrieve a list of Acme employees who have submitted resumes to personnel for a different job placement.

Enter this query:

SELECT   EID, ENAME
     FROM     EMPLOYEE
     WHERE    EXISTS
       (SELECT RNAME
        FROM   RESUME
        WHERE  EMPLOYEE.ENAME = RESUME.RNAME) ;

The results are:

EID  ENAME
  107  Smith, Roberta
  113  Ferrer, Miguel

In this query, the subquery cannot be evaluated completely before the outer query is evaluated. Instead, we have a correlated subquery. For each row in EMPLOYEE, a join of EMPLOYEE and RESUME tables is performed (even though RESUME is the only table that appears in the subquery's FROM clause) to determine if there is a resume name in RESUME that matches a name in EMPLOYEE.

For example, for the first row in the EMPLOYEE table (ENAME = 'Smith, Roberta') the subquery evaluates as "true" if at least one row in the RESUME table has RNAME = 'Smith, Roberta'; otherwise, the expression evaluates as "false". Since there is a row in RESUME with RNAME = 'Smith, Roberta', the expression is true and Roberta Smith's row is displayed. Each row in EMPLOYEE is evaluated in a similar manner.

The following is an example of the interim join (in shortened form) between the EMPLOYEE and RESUME Tables, for the above names which satisfied the search requirement by appearing in both tables:

EMPLOYEE Table            RESUME Table
  EID ENAME          DEPT   RES_ID  RNAME           JOB       EXISTS
                                                              (subquery)
  107 Smith, Roberta RND    R406    Smith, Roberta  Engineer  TRUE
  113 Ferrer, Miguel CSS    R425    Ferrer, Miguel  Analyst   TRUE

Note in this example that there is no key ID field connecting the two tables; therefore the character field for name is being used to join the two tables, which might have been entered differently and therefore is not an altogether reliable join. This indicates that such a search is an unusual rather than a usual action.

Such a search would be a good opportunity to use a Metamorph LIKE qualifier rather than a straight join on a column as above, where ENAME must match exactly RNAME. A slightly more thorough way of searching for names appearing in both tables which were not necessarily intended to be matched exactly would use Metamorph's approximate pattern matcher, indicated by a percent sign % preceding the name. For example:

SELECT   EID, ENAME
     FROM     EMPLOYEE
     WHERE    EXISTS
       (SELECT *
        FROM   RESUME
        WHERE  EMPLOYEE.ENAME LIKE '%' + RESUME.RNAME) ;

In this example a name approximately like each RNAME in the RESUME table would be compared to each ENAME in the EMPLOYEE table, increasing the likelihood of a match. (String concatenation is used to append the name found in the resume table to the percent sign (%) which signals the approximate pattern matcher XPM.)

Often, a query is formed to test if no rows are returned in a subquery. In this case, the following form of the existence test is used:

WHERE   NOT EXISTS (subquery)

Example: List any authors of reports submitted to the online corporate library who are not current employees of Acme Industrial. To find this out we would need to know which authors listed in the REPORT table are not entered as employees in the EMPLOYEE table.

Use this query:

SELECT   AUTHOR
     FROM     REPORT
     WHERE    NOT EXISTS
       (SELECT *
        FROM   EMPLOYEE
        WHERE  EMPLOYEE.ENAME = REPORT.AUTHOR) ;

which would likely result in a list of former employees such as:

AUTHOR
  Acme, John Jacob Snr.
  Barrington, Cedrick II.
  Rockefeller, George G.

Again, we have an example of a correlated subquery. Below is illustrated (in shortened form) how each row which satisfied the search requirement above in REPORT is evaluated with the records in EMPLOYEE to determine which authors are not (or are no longer) Acme employees.

REPORT Table                               EMPLOYEE Table  EXISTS
  TITLE              AUTHOR
  Company Origin     Acme, John Jacob Snr.                   FALSE
  Management Art     Barrington, Cedrick II.                 FALSE
  Financial Control  Rockefeller, George G.                  FALSE

In this example each of the above authors from the REPORT Table are tested for existence in the EMPLOYEE Table. When they are not found to exist there it returns a value of FALSE. Since the query condition in the WHERE clause is that it NOT EXISTS, this changes the false value to true, and these rows are displayed.

For each of the queries shown in this section, there are probably several ways to obtain the same kind of result. Some correlated subqueries can also be expressed as joins. These examples are given not so much as the only definitive way to state these search requests, but more so as to give a model for what kinds of things are possible.