DB2 for z/OS - Subqueries

You can use a subquery to narrow a search condition that is based on information in an interim table.

A subquery is a nested SQL statement, or subselect, that contains a SELECT statement within the WHERE or HAVING clause of another SQL statement. You can also code more complex subqueries, such as correlated subqueries and subqueries with quantified predicates.

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

Example: Suppose that you want a list of the employee numbers, names, and commissions of all employees that work on a particular project, such as project number IF2000. The first part of the SELECT statement is easy to write:

SELECT EMPNO, LASTNAME, COMM
  FROM EMP
  WHERE EMPNO
⋮

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

You can use a subselect to solve this problem. The SELECT statement that surrounds the subquery is the outer SELECT.

Example: This query expands the SELECT statement that started in the previous example to include a subquery:

SELECT EMPNO, LASTNAME, COMM
  FROM EMP
  WHERE EMPNO IN
    (SELECT EMPNO
       FROM EMPPROJACT
       WHERE PROJNO = 'IF2000');

To better understand what happens as a result from this SQL statement, imagine that DB2® goes through the following process:

DB2 evaluates the subquery to obtain a list of EMPNO values:
```
(SELECT EMPNO
   FROM EMPPROJACT
   WHERE PROJNO = 'IF2000');
```
The result is the following interim result table:
```
EMPNO
======
000140
000140
000030
```

The interim result table then serves as a list in the search condition of the outer SELECT. Effectively, DB2 runs this SELECT statement:

SELECT EMPNO, LASTNAME, COMM
  FROM EMP
  WHERE EMPNO IN
  ('000140', '000030');

The result table looks like this:

EMPNO     LASTNAME        COMM
=====     ========     =======
000140    NICHOLLS     2274.00
000030    KWAN         3060.00