Which special operator is used to check whether a subquery returns any rows?

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)

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.

Note: 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');