Single Value Comparisons

In those cases where a nested query can only retrieve one value (as with an aggregate function), the special operators SOME, ANY and ALL may be omitted from the comparison condition.

The next sample query finds the names of all suppliers with a status greater than the average supplier status:

SELECT sname
FROM supplier
WHERE status >
  (SELECT AVG(status)
  FROM supplier)

The results of this query follow:

sname
BLAKE
ADAMS
Parent topic: Nested Queries