16.3. The ALL Predicate and Extrema Functions
It is counterintuitive at first that these two predicates are not the same in SQL:
x >= (SELECT MAX(y) FROM Table1) x >= ALL (SELECT y FROM Table1)
But you have to remember the rules for the extrema functions—they drop out all the NULLs before returning the greater or least values. The ALL predicate does not drop NULLs, so you can get them in the results.
However, if you know that there are no NULLs in a column, or are willing to drop the NULLs yourself, then you can use the ALL predicate to construct single queries to do work that would otherwise be done by two queries. For example, we could use the table of products and store managers we used earlier in this chapter and find which manager handles ...
Get Joe Celko's SQL for Smarties, 3rd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
