Practical SQL Handbook, The: Using SQL Variants, Fourth Edition

Subqueries Introduced with Comparison Operators and ANY or ALL

Another kind of subquery, which returns any number of rows (including none) for the outer query, uses a comparison operator modified with keywords ALL or ANY. Subqueries introduced with a modified comparison operator take the following general form:

						Start of SELECT, INSERT, UPDATE, DELETE statement; or
						subquery
WHERE expression comparison_operator [ANY | ALL]
     (subquery)
[End of SELECT, INSERT, UPDATE, DELETE statement; or subquery]
					

Understanding ALL and ANY

Using the “>” comparison operator as an example, “> ALL” means greater than every value—in other words, greater than the largest value. In this way, “> ALL (1, 2, 3)” means greater than 3. “> ANY” means greater than at least ...