Including subqueries in the WHERE or HAVING clause

 

You can include a subquery in a WHERE or HAVING clause by using a basic or quantified comparison, the IN keyword, or the EXISTS keyword.

 

Basic comparisons

You can use a subquery before or after any of the comparison operators. The subquery can return only one row. It can return multiple values for the row if the equal or not equal operators are used. SQL compares each value from the subquery row with the corresponding value on the other side of the comparison operator. For example, suppose that you want to find the employee numbers, names, and salaries for employees whose education level is higher than the average education level throughout the company.

     SELECT EMPNO, LASTNAME, SALARY      FROM CORPDATA.EMPLOYEE      WHERE EDLEVEL >
        (SELECT AVG(EDLEVEL)
           FROM CORPDATA.EMPLOYEE)

SQL first evaluates the subquery and then substitutes the result in the WHERE clause of the SELECT statement. In this example, the result is the company-wide average educational level. Besides returning a single row, a subquery can return no rows. If it does, the result of the compare is unknown.

 

Quantified comparisons (ALL, ANY, and SOME)

You can use a subquery after a comparison operator followed by the keyword ALL, ANY, or SOME. When used in this way, the subquery can return zero, one, or many rows, including null values. You can use ALL, ANY, and SOME in the following ways:

• Use ALL to indicate that the value you supplied must compare in the indicated way to ALL the rows the subquery returns. For example, suppose you use the greater-than comparison operator with ALL:

  … WHERE expression > ALL (subquery)

  To satisfy this WHERE clause, the value of the expression must be greater than the result for each of the rows (that is, greater than the highest value) returned by the subquery. If the subquery returns an empty set (that is, no rows were selected), the condition is satisfied.

• Use ANY or SOME to indicate that the value you supplied must compare in the indicated way to at least one of the rows the subquery returns. For example, suppose 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 rows (that is, greater than the lowest value) returned by the subquery. If what the subquery returns is the empty set, the condition is not satisfied.

The results when a subquery returns one or more null values may surprise you, unless you are familiar with formal logic.

 

IN keyword

You can use IN to say that the value in the expression must be among the rows returned by the subquery. Using IN is equivalent to using =ANY or =SOME. Using ANY and SOME were previously described. You can also use the IN keyword with the NOT keyword in order to select rows when the value is not among the rows returned by the subquery. For example, you can use:

… WHERE WORKDEPT NOT IN (SELECT …)

 

EXISTS keyword

In the subqueries presented so far, SQL evaluates the subquery and uses the result as part of the WHERE clause of the outer-level SELECT. In contrast, when you use the keyword EXISTS, SQL checks whether the subquery returns one or more rows. If it does, the condition is satisfied. If it returns no rows, the condition is not satisfied. For example:

     SELECT EMPNO,LASTNAME      FROM CORPDATA.EMPLOYEE      WHERE EXISTS
        (SELECT *
           FROM CORPDATA.PROJECT            WHERE PRSTDATE > '1982-01-01');

In the example, the search condition is true if any project represented in the CORPDATA.PROJECT table has an estimated start date that is later than January 1, 1982. This example does not show the full power of EXISTS, because the result is always the same for every row examined for the outer-level SELECT. As a consequence, either every row appears in the results, or none appear. In a more powerful example, the subquery itself would be correlated, and change from row to row.

As shown in the example, you do not need to specify column names in the select-list of the subquery of an EXISTS clause. Instead, you should code SELECT *.

You can also use the EXISTS keyword with the NOT keyword in order to select rows when the data or condition you specify does not exist. You can use the following:

… WHERE NOT EXISTS (SELECT …)

 

Parent topic:

Subqueries in SELECT statements