Subqueries with any and all

The keywords all and any can modify comparison operators that introduce a subquery.

When any is used with <, >, or = with a subquery, it returns results when any value retrieved in the subquery matches the value in the where or having clause of the outer statement.

When all is used with < or > in a subquery, it returns results when all values retrieved in the subquery match the value in the where or having clause of the outer statement.

The syntax for any and all is:

{where | having} [not] 
	expression comparison_operator {any | all} (subquery) 

If you introduce a subquery with all and a comparison operator does not return any values, the entire query fails.

all and any can be tricky. For example, you might ask “Which books commanded an advance greater than any book published by New Age Books?”

You can paraphrase this question to make its SQL “translation” more clear: “Which books commanded an advance greater than the largest advance paid by New Age Books?” The all keyword, not the any keyword, is required here:

select title 
from titles 
where advance > all 
   (select advance 
    from publishers, titles 
    where titles.pub_id = publishers.pub_id 
    and pub_name = "New Age Books") 

title 
---------------------------------------- 
The Gourmet Microwave 

For each title, the outer query gets the titles and advances from the titles table, and it compares these to the advance amounts paid by New Age Books returned from the subquery. The outer query looks at the largest value in the list and determines whether the title being considered has commanded an even greater advance.

• > all Means Greater Than All Values
  The > all operator means that, for a row to satisfy the condition in the outer query, the value in the column that introduces the subquery must be greater than each of the values returned by the subquery.
• = all Means Equal to Every Value
  The = all operator means that for a row to satisfy the outer query the value in the column that introduces the subquery must be the same as each value in the list of values returned by the subquery.
• > any Means Greater Than at Least One Value
  > any means that, for a row to satisfy the outer query, the value in the column that introduces the subquery must be greater than at least one of the values in the list returned by the subquery.
• = any Means Equal to Some Value
  The = any operator is an existence check; it is equivalent to in.