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.
For example, to find the books that are priced higher than the highest-priced book in the mod_cook category:
select title from titles where price > all (select price from titles where type = "mod_cook")
title --------------------------------------------------- But Is It User Friendly? Secrets of Silicon Valley Computer Phobic and Non-Phobic Individuals: Behavior Variations Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean (4 rows affected)
However, if the set returned by the inner query contains a NULL, the query returns 0 rows. This is because NULL stands for “value unknown,” and it is impossible to tell whether the value you are comparing is greater than an unknown value.
For example, try to find the books that are priced higher than the highest-priced book in the popular_comp category:
select title from titles where price > all (select price from titles where type = "popular_comp")
title --------------------------------------------------- (0 rows affected)
No rows are returned because the subquery finds that one of the books, Net Etiquette, has a null price.