In working with data from a subselect, you need to deal with ambiguous situations where in some cases
the subselect will return a single (scalar) value, and in other cases multiple values.
Oracle supports three expressions that allow a subselect to be compared based on a single column of
results. The operators ANY, SOME, and ALL allow one or more single-column values from a subselect to be
compared to data in an outer SELECT. Using these operators allows you to deal with situations where
you’d like to code your SQL to handle comparisons with flexible set sizes.
Our recipe focuses on using these expressions for a concrete business problem. The order-entry
system tracks product information in the OE.PRODUCT_INFORMATION table, including the LIST_PRICE value.
However, we know discounts are often offered, so we’d like to get an approximate idea of which items
have never sold at full price. To do this, we could do a precise correlated subquery of every sale against
list price. Before doing that, a very quick approximation can be done to see if any LIST_PRICE value is
higher than any known sale price for any item, indicated by the UNIT_PRICE column of the
OE.ORDER_ITEMS table. Our SELECT statement takes this form.
select product_id, product_name
where list_price > ALL
From this query, we see three results:
2351 Desk – W/48/R
3003 Laptop 128/12/56/v90/110
2779 Desk – OS/O/F
These results mean at least three items—two desks and a laptop—have never sold a full price.