Subqueries

When you need to narrow your search condition based on information in an interim table, you can use a subquery. For example, you might want to find all employee numbers in one table that also exist for a given project in a second table.

Conceptual overview of subqueries

Suppose that you want a list of the employee numbers, names, and commissions of all employees who work on a particular project, whose project number is MA2111. The first part of the SELECT statement is easy to write: 
SELECT EMPNO, LASTNAME, COMM
  FROM DSN8A10.EMP
  WHERE EMPNO
  ⋮

However, you cannot proceed because the DSN8A10.EMP table does not include project number data. You do not know which employees are working on project MA2111 without issuing another SELECT statement against the DSN8A10.EMPPROJACT table.

You can use a subquery to solve this problem. A subquery is a subselect or a fullselect in a WHERE clause. The SELECT statement that surrounds the subquery is called the outer SELECT.
SELECT EMPNO, LASTNAME, COMM
  FROM DSN8A10.EMP
  WHERE EMPNO IN
    (SELECT EMPNO
       FROM DSN8A10.EMPPROJACT
        WHERE PROJNO = 'MA2111');
To better understand the results of this SQL statement, imagine that DB2® goes through the following process:
  1. DB2 evaluates the subquery to obtain a list of EMPNO values: 
    (SELECT EMPNO
   FROM DSN8A10.EMPPROJACT
   WHERE PROJNO = 'MA2111');
    The result is in an interim result table, similar to the one in the following output: 
    from EMPNO 
     =====
     200
     200
     220
  2. The interim result table then serves as a list in the search condition of the outer SELECT. Effectively, DB2 executes this statement: 
    SELECT EMPNO, LASTNAME, COMM
  FROM DSN8A10.EMP
  WHERE EMPNO IN
  ('000200', '000220');
    As a consequence, the result table looks similar to the following output: 
    EMPNO   LASTNAME  COMM
======  ========  ====
000200  BROWN     2217
000220  LUTZ      2387

Correlated and uncorrelated subqueries

Subqueries supply information that is needed to qualify a row (in a WHERE clause) or a group of rows (in a HAVING clause). The subquery produces a result table that is used to qualify the row or group of selected rows.

A subquery executes only once, if the subquery is the same for every row or group. This kind of subquery is uncorrelated, which means that it executes only once. For example, in the following statement, the content of the subquery is the same for every row of the table DSN8A10.EMP: 
SELECT EMPNO, LASTNAME, COMM
  FROM DSN8A10.EMP
  WHERE EMPNO IN
    (SELECT EMPNO
       FROM DSN8A10.EMPPROJACT
        WHERE PROJNO = 'MA2111');

Subqueries that vary in content from row to row or group to group are correlated subqueries. For information about correlated subqueries, see Correlated subqueries.

Subqueries and predicates

A predicate is an element of a search condition that specifies a condition that is true, false, or unknown about a given row or group. A subquery, which is a SELECT statement within the WHERE or HAVING clause of another SQL statement, is always part of a predicate. The predicate is of the form: 
operand operator (subquery)
A WHERE or HAVING clause can include predicates that contain subqueries. A predicate that contains a subquery, like any other search predicate, can be enclosed in parentheses, can be preceded by the keyword NOT, and can be linked to other predicates through the keywords AND and OR. For example, the WHERE clause of a query can look something like the following clause: 
WHERE X IN (subquery1) AND (Y > SOME (subquery2) OR Z IS NULL)

Subqueries can also appear in the predicates of other subqueries. Such subqueries are nested subqueries at some level of nesting. For example, a subquery within a subquery within an outer SELECT has a nesting level of 2. DB2 allows nesting down to a level of 15, but few queries require a nesting level greater than 1.

The relationship of a subquery to its outer SELECT is the same as the relationship of a nested subquery to a subquery, and the same rules apply, except where otherwise noted.

The subquery result table

A subquery must produce a result table that has the same number of columns as the number of columns on the left side of the comparison operator. For example, both of the following SELECT statements are acceptable: 
SELECT EMPNO, LASTNAME 
  FROM DSN8A10.EMP
  WHERE SALARY =
  (SELECT AVG(SALARY) 
    FROM DSN8A10.EMP);
SELECT EMPNO, LASTNAME 
  FROM DSN8A10.EMP
  WHERE (SALARY, BONUS) IN
  (SELECT AVG(SALARY), AVG(BONUS) 
    FROM DSN8A10.EMP);

Except for a subquery of a basic predicate, the result table can contain more than one row. For more information, see Places where you can include a subquery.