> > select * from t1 where a >= ALL (select b from t2)
>
> What are these constructs suppose to do?
"""
QUANTIFIED SUBQUERIES
A quantified subquery allows several types of tests and
can use the full set of comparison operators. It has
the following general format:
value-1 {=|>|<|>=|<=|<>} {ALL|ANY|SOME} (query-1)
The comparison operator specifies how to compare value-1
to the single query column value from each subquery
result row. The ANY, ALL, SOME specifiers give the type
of match expected. ANY and SOME must match at least one row
in the subquery. ALL must match all rows in the subquery,
or the subquery must be empty (produce no rows).
"""
The next two WHERE condition are equivalent:
total_price > ALL (SELECT total_price FROM items
WHERE order_num = 1023)
total_price > (SELECT MAX(total_price) FROM items
WHERE order_num = 1023)
The next two WHERE condition are equivalent also:
total_price > ANY (SELECT total_price FROM items
WHERE order_num = 1023)
total_price > (SELECT MIN(total_price) FROM items
WHERE order_num = 1023)
ANY and SOME are synonyms.
The next example show real use-case.
Table: Assemblies
+----------------+--------------+--------------+
| AssemblyName | PartNumber | PartWeight |
+----------------+--------------+--------------+
| Assembly1 | 01 | 100 |
| Assembly1 | 02 | 150 |
| Assembly2 | 01 | 120 |
| ... | ... | ... |
Query: Assemblies with max total weight:
Because SQL doesn't allow nested aggregated functions
MAX(SUM(...)), it is easy for some people
write this query as:
select AssemblyName from Assemblies
group by AssemblyName
having SUM(PartWeight) >= ALL (
select SUM(PartWeight)
from T1
group by AssemblyName
)
Of course, this query may be reformulated as:
select TOP 1 AssemblyName from Assemblies
group by AssemblyName
order by SUM(PartWeight) DESC
> x <op> all (select y from t where ...)
>
> is equivalent to
>
> not exists (select y from t where not (x <op> y) and ...)
>
> Any and some are synonyms.
>
> x <op> any (select y from t where ...)
>
> is equivalent to
>
> exists (select y from t where x <op> y and ...)
Nice summary
Best regards,
Alexander mailto:[EMAIL PROTECTED]
