I’ll be honest, I thought I knew all there was to know about sub-queries. Turns out, there is something new (at least to me). You can modify your sub-query to allow you to pass multiple results. The way you do this is you add (ANY | SOME) or ALL to the query. Let me illustrate.
SELECT Name
FROM kotor.item
WHERE ListPrice >= ANY
(SELECT MAX (price)
FROM kotor.item
GROUP BY ItemCategory)
In this query we’re asking the server to show us the name of all items in our kotor items table where the price is over the MAX(price) for an item’s category. This may sound odd, but think about it. Now we aren’t joining to the sub-query, so we’re not matching on ItemCategory.
What we are doing is showing the names where they cost more than any one category’s MAX(price).
ANY and SOME are equivalent statements.
By adding in this modifier your sub-queries can now use GROUP BY and HAVING clauses.
One of the reasons I haven’t used this, is you can often rewrite these queries so you don’t have to use the modifier. Let’s say you wanted to see those items that were had a price greater than or equal to ALL the max prices by group.
SELECT Name
FROM kotor.item
WHERE ListPrice >= ALL
(SELECT MAX (price)
FROM kotor.item
GROUP BY ItemCategory)
In this case you want to see those items that are greater than or equal to the MAX of the maxes. (sounds silly, but that has been the norm for my queries.) You could rewrite it:
SELECT Name
FROM kotor.item
WHERE ListPrice >=
(SELECT MAX (price)
FROM kotor.item )
Now it only retrieves the absolute MAX…no need for the grouping any more, and in turn, no need for the modifier.
Rather than using >, < >=, <=, you can also use the modifiers with =. The reason I haven't used it in the past is = ANY is equivalent to using IN. Although thinking about using = ALL would be very odd syntactically, since how you you have a single record with multiple values? Definitely worth investigating. You can also use modifiers on <> or !=. <> ANY would be not any one value. Let’s say you had 3 values in the result of your sub-query a, b, c. The logic <> AND would mean != a OR != b OR !=C. So you would get records that have a value of a, b, or c, since a != b, you get a, since it does pass the logic. It’s this kind of brain twist that makes me not use the modifier!
But, <> ALL or != ALL is equivalent to NOT IN. So I choose to use NOT IN instead…It makes more sense to me!