SQL: ALL is one of the least understood T-SQL logical operators

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:

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.

2021-03-04