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
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.
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 ...)