Places where you can include a subquery

You can specify a subquery in either a WHERE clause or a HAVING clause.

You can specify a subquery in either a WHERE or HAVING clause by using one of the following items:

Example: Basic predicate in a subquery

You can use a subquery immediately after any of the comparison operators. If you do, the subquery can return at most one value. Db2 compares that value with the value to the left of the comparison operator.

The following SQL statement returns the employee numbers, names, and salaries for employees whose education level is higher than the average company-wide education level.

SELECT EMPNO, LASTNAME, SALARY
  FROM DSN8C10.EMP
  WHERE EDLEVEL >
    (SELECT AVG(EDLEVEL)
       FROM DSN8C10.EMP);

Example: Quantified predicate in a subquery: ALL, ANY, or SOME

You can use a subquery after a comparison operator, followed by the keyword ALL, ANY, or SOME. The number of columns and rows that the subquery can return for a quantified predicate depends on the type of quantified predicate:

For = SOME, = ANY, or <> ALL, the subquery can return one or many rows and one or many columns. The number of columns in the result table must match the number of columns on the left side of the operator.
For all other quantified predicates, the subquery can return one or many rows, but no more than one column.

See the information about quantified predicates, including what to do if a subquery that returns one or more null values gives you unexpected results.

Example: ALL predicate

Use ALL to indicate that the operands on the left side of the comparison must compare in the same way with all of the values that the subquery returns. For example, suppose that you use the greater-than comparison operator with ALL:

WHERE column > ALL (subquery)

To satisfy this WHERE clause, the column value must be greater than all of the values that the subquery returns. A subquery that returns an empty result table satisfies the predicate.

Now suppose that you use the <> operator with ALL in a WHERE clause like this:

WHERE (column1, column1, ... columnn) <> ALL (subquery)

To satisfy this WHERE clause, each column value must be unequal to all of the values in the corresponding column of the result table that the subquery returns. A subquery that returns an empty result table satisfies the predicate.

Example: ANY or SOME predicate

Use ANY or SOME to indicate that the values on the left side of the operator must compare in the indicated way to at least one of the values that the subquery returns. For example, suppose that you use the greater-than comparison operator with ANY:

WHERE expression > ANY (subquery)

To satisfy this WHERE clause, the value in the expression must be greater than at least one of the values (that is, greater than the lowest value) that the subquery returns. A subquery that returns an empty result table does not satisfy the predicate.

Now suppose that you use the = operator with SOME in a WHERE clause like this:

WHERE (column1, column1, ... columnn) = SOME (subquery)

To satisfy this WHERE clause, each column value must be equal to at least one of the values in the corresponding column of the result table that the subquery returns. A subquery that returns an empty result table does not satisfy the predicate.

Example: IN predicate in a subquery

You can use IN to say that the value or values on the left side of the IN operator must be among the values that are returned by the subquery. Using IN is equivalent to using = ANY or = SOME.

The following query returns the names of department managers:

SELECT EMPNO,LASTNAME
  FROM DSN8C10.EMP
    WHERE EMPNO IN
      (SELECT DISTINCT MGRNO
         FROM DSN8C10.DEPT);

EXISTS predicate in a subquery

When you use the keyword EXISTS, Db2 checks whether the subquery returns one or more rows. Returning one or more rows satisfies the condition; returning no rows does not satisfy the condition.

The search condition in the following query is satisfied if any project that is represented in the project table has an estimated start date that is later than 1 January 2005:

SELECT EMPNO,LASTNAME
  FROM DSN8C10.EMP
    WHERE EXISTS
      (SELECT *
         FROM DSN8C10.PROJ
         WHERE PRSTDATE > '2005-01-01');

The result of the subquery is always the same for every row that is examined for the outer SELECT. Therefore, either every row appears in the result of the outer SELECT or none appears. A correlated subquery is more powerful than the uncorrelated subquery that is used in this example because the result of a correlated subquery is evaluated for each row of the outer SELECT.

As shown in the example, you do not need to specify column names in the subquery of an EXISTS clause. Instead, you can code SELECT *. You can also use the EXISTS keyword with the NOT keyword in order to select rows when the data or condition that you specify does not exist; that is, you can code the following clause:

WHERE NOT EXISTS (SELECT ...);