You can use a subquery to test for the existence of a row that satisfies some condition. In this case, the subquery is linked to the outer-level query by the predicate EXISTS or NOT EXISTS.
When you link a subquery to an outer query by an EXISTS predicate, the subquery does not return a value. Rather, the EXISTS predicate is true if the answer set of the subquery contains one or more rows, and false if it contains no rows.
The EXISTS predicate is often used with correlated subqueries. The example below lists the departments that currently have no entries in the PROJECT table:
SELECT DEPTNO, DEPTNAME
FROM DEPARTMENT X
WHERE NOT EXISTS
(SELECT *
FROM PROJECT
WHERE DEPTNO = X.DEPTNO)
ORDER BY DEPTNO
You may connect the EXISTS and NOT EXISTS predicates to other predicates by using AND and OR in the WHERE clause of the outer-level query.
Quantified Predicates All & Any
A quantified predicate compares a value with a collection of values. If a fullselect returns more than one value, you must modify the comparison operators in your predicate by attaching the suffix ALL, ANY, or SOME. These suffixes determine how the set of values returned is to be treated in the outer-level predicate. The > comparison operator is used as an example (the remarks below apply to the other operators as well):
expression > ALL (fullselect)
The predicate is true if the expression is greater than each individual value returned by the fullselect. If the fullselect returns no values, the predicate is true. The result is false if the specified relationship is false for at least one value. Note that the <>ALL quantified predicate is equivalent to the NOT IN predicate.
The following example uses a subquery and a > ALL comparison to find the name and profession of all employees who earn more than all managers:
SELECT LASTNAME, JOB
FROM EMPLOYEE
WHERE SALARY > ALL
(SELECT SALARY
FROM EMPLOYEE
WHERE JOB='MANAGER')
expression > ANY (fullselect)
The predicate is true if the expression is greater than at least one of the values returned by the fullselect. If the fullselect returns no values, the predicate is false. Note that the =ANY quantified operator is equivalent to the IN predicate.
expression > SOME (fullselect)
SOME is synonymous with ANY.
.
