I was answering a question in a forum the other day. I was asked if SQL Server and T-SQL had the ALL operator. It does, yet few people seem to either know about it or how to use it.
The basic idea of the ALL operator is to allow you to compare a single value to a set of values, using a logical operation. For example, in the query below:
|
1 2 3 4 |
IF 12 > ALL (SELECT * FROM (VALUES(1), (2), (3), (4)) AS v(Value)) BEGIN PRINT 'Larger'; END; |
I've got a subquery that returns the values 1, 2, 3, and 4. I'm asking if 12 is greater than all of them.
The main image above shows a more practical use of ALL.
In that image, I'm querying the Sales.Orders table in WideWorldImporters to find the oldest outstanding order where we could currently supply all the items requested on the order.
I do that by calculating the total of what we have in stock for all items on the order, and substracting how many are on the order. I then just check if all of balances are then at least 0. That means we have all the items.
I could have written this as a NOT EXISTS query instead but ALL is a pretty clean way to write it.
Hi there –
1) Is (If 12>ALL…) the same as (If 12 > (SELECT max(v.Value) FROM (VALUES(1), (2), (3), (4)) AS v(Value))? To me they're logically equivalent, or am I missing something?
2) How would you write the equivalent query using an If exists? I interpreted that you meant 12 not exists in values 1,2,3,4. And while that answer is true, it is not true if you had a 12 in your list of values.
Hi m, 1) for that one, yes you could do that. Similar logic works for = though, not just for >. So you can check if every value is 12. 2) Instead of 12 > ALL, you could say WHERE NOT EXISTS (SELECT 1 …. WHERE value <= 12)