The ANY, ALL, and SOME operators in SQL Server are used to compare a value to a set of values.
The SOME operator is a synonym for ANY and can be used interchangeably.
These operators can be used in a variety of ways, such as with subqueries, to filter and retrieve specific data from a table. It’s important to keep in mind that the ANY, ALL, and SOME operators can only be used with a subquery and can’t be used with a list of values or a constant.
Because they can be used with comparison operators and the AND, OR and NOT logical operators, there are quite a few permutations in which they can be used. Overall, I recommend only using ANY and ALL if performance dictates, as they often require a bit of extra thinking to understand.
For this blog I have broken down the forms which are most relevant to understanding their usage and an equivalent statement that produces the same result. Hopefully I have everything correct here, leave a message if you spot anything I missed.
So, here is a table of 8 operations and their equivalent statements. If you understand these 9, you should be able to easily understand any permutation of ANY, ALL with the various comparison and logical operators.
The comparison <> ANY has several equivalents which I have included.
| ID | Operation | Equivalent |
|---|---|---|
| 1 | = ALL | = MIN AND = MAX |
| 2 | <> ALL | NOT IN |
| 3 | > ALL | > MAX |
| 4 | < ALL | < MIN |
| 5 | #NAME? | IN |
| 6 | <> ANY | NOT(= ALL), <> MIN OR <> MAX, TOP 1 |
| 7 | > ANY | > MIN |
| 8 | < ANY | < MAX |
| 9 | >= ANY AND <= ANY | BETWEEN and the MIN/MAX functions |
I find using the IF statement to be the most helpful in understanding the ANY and ALL syntax. Here I break down the above table into multiple queries.
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
-- PART 1
-- EQUAL TO ALL
--FALSE
--3 does not equate to ALL values in the comparison set
IF 3 = ALL (SELECT ID FROM (VALUES(1),(2),(3),(4)) AS a(ID))
PRINT 'TRUE'
ELSE
PRINT 'FALSE';
--TRUE
--3 does equate to ALL values in the comparison set
IF 3 = ALL (SELECT ID FROM (VALUES(3),(3),(3),(3)) AS a(ID))
PRINT 'TRUE'
ELSE
PRINT 'FALSE';
----------------------------------------------------------------------
-- TRUE
-- = ALL equivalent using MIN and MAX
-- 3 is equal to the MIN and MAX values of the comparison set
IF 3 = (SELECT MAX(ID) FROM (VALUES(3),(3),(3),(3)) AS a(ID))
AND
3 = (SELECT MIN(ID) FROM (VALUES(3),(3),(3),(3)) AS a(ID))
PRINT 'TRUE'
ELSE
PRINT 'FALSE';
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
-- PART 2
-- NOT EQUAL TO ALL
--FALSE
--3 is IN the comparison set
IF 3 <> ALL (SELECT ID FROM (VALUES(1),(2),(3),(4)) AS a(ID))
PRINT 'TRUE'
ELSE
PRINT 'FALSE';
-- TRUE
-- 5 is NOT IN the comparison set
IF 5 <> ALL (SELECT ID FROM (VALUES(1),(2),(3),(4)) AS a(ID))
PRINT 'TRUE'
ELSE
PRINT 'FALSE';
----------------------------------------------------------------------
--FALSE
-- <> ALL equivalent using the NOT IN operator
-- 3 is IN the comparison set
IF 3 NOT IN (SELECT ID FROM (VALUES(1),(2),(3),(4)) AS a(ID))
PRINT 'TRUE'
ELSE
PRINT 'FALSE';
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
-- PART 3
-- GREATER THAN ALL
-- FALSE
-- 3 is not greater than the MAX value in the comparison set
IF 3 > ALL (SELECT ID FROM (VALUES(1),(2),(3),(4)) AS a(ID))
PRINT 'TRUE'
ELSE
PRINT 'FALSE' ;
----------------------------------------------------------------------
-- TRUE
-- > ALL equivalent using MAX
-- 5 is greater than the MAX value in the comparison set
IF 5 > (SELECT MAX(ID) FROM (VALUES(1),(2),(3),(4)) AS a(ID))
PRINT 'TRUE'
ELSE
PRINT 'FALSE';
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
-- PART 4
-- LESS THAN ALL
-- FALSE
--3 is not less than the MIN value in the comparison set
IF 3 < ALL (SELECT ID FROM (VALUES(1),(2),(3),(4)) AS a(ID))
PRINT 'TRUE'
ELSE
PRINT 'FALSE';
----------------------------------------------------------------------
-- TRUE
-- < ALL equivalent using MIN
-- 1 is less than the MIN value in the comparison set
IF 1 < (SELECT MIN(ID) FROM (VALUES(1),(2),(3),(4)) AS a(ID))
PRINT 'TRUE'
ELSE
PRINT 'FALSE';
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
-- PART 5
-- EQUAL TO ANY
--TRUE
--3 matches at least one value in the comparison set
IF 3 = ANY (SELECT ID FROM (VALUES(1),(2),(3),(4)) AS a(ID))
PRINT 'TRUE'
ELSE
PRINT 'FALSE';
----------------------------------------------------------------------
-- TRUE
-- = ANY equivalent using the IN operator
-- 3 is IN the comparison set
IF 3 IN (SELECT ID FROM (VALUES(1),(2),(3),(4)) AS a(ID))
PRINT 'TRUE'
ELSE
PRINT 'FALSE';
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
-- PART 6
-- NOT EQUAL TO ANY
-- Note this has several equivalent statements
-- TRUE
-- 3 is not equal to 1, 2 and 4
IF 3 <> ANY (SELECT ID FROM (VALUES(1),(2),(3),(4)) AS a(ID))
PRINT 'TRUE'
ELSE
PRINT 'FALSE';
-- FALSE
-- 3 is equal to every value in the statement
IF 3 <> ANY (SELECT ID FROM (VALUES(3),(3),(3),(3)) AS a(ID))
PRINT 'TRUE'
ELSE
PRINT 'FALSE';
----------------------------------------------------------------------
-- TRUE
-- <> ANY equivalent using NOT(= ALL)
IF NOT(3 = ALL(SELECT ID FROM (VALUES(1),(2),(3),(4)) AS a(ID)))
PRINT 'TRUE'
ELSE
PRINT 'FALSE';
----------------------------------------------------------------------
--TRUE
-- <> ANY equivalent using <> MIN OR <> MAX
-- 3 is not equal to the MAX or the MIN values of the comparison set
IF 3 <> (SELECT MAX(ID) FROM (VALUES(1),(2),(3),(4)) AS a(ID))
OR
3 <> (SELECT MIN(ID) FROM (VALUES(1),(2),(3),(4)) AS a(ID))
PRINT 'TRUE'
ELSE
PRINT 'FALSE';
----------------------------------------------------------------------
-- TRUE
-- <> ANY equivalent using DISTINCT, CROSS JOIN, and a theta-join
SELECT DISTINCT TableA.ID
FROM (VALUES(3)) AS TableA(ID) CROSS JOIN
(VALUES(1),(2),(3)) AS TableB(ID)
WHERE TableA.ID <> TableB.ID;
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
-- PART 7
-- GREATER THAN ANY
-- TRUE
-- 3 is greater than the MIN value in the comparison set
IF 3 > ANY (SELECT ID FROM (VALUES(1),(2),(3),(4)) AS a(ID))
PRINT 'TRUE'
ELSE
PRINT 'FALSE';
----------------------------------------------------------------------
-- TRUE
-- > ANY equivalent using MIN function
-- 3 is greater than the MIN value in the comparison set
IF 3 > (SELECT MIN(ID)a FROM (VALUES(1),(2),(3),(4)) AS a(ID))
PRINT 'TRUE'
ELSE
PRINT 'FALSE';
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
-- PART 8
-- LESS THAN ANY
-- TRUE
-- 3 is less than the MAX value in the comparison set
IF 3 < ANY (SELECT ID FROM (VALUES(1),(2),(3),(4)) AS a(ID))
PRINT 'TRUE'
ELSE
PRINT 'FALSE';
----------------------------------------------------------------------
-- TRUE
-- < ANY equivalent using MAX
-- 3 is less than the MAX value in the comparison set
IF 3 < (SELECT MAX(ID)a FROM (VALUES(1),(2),(3),(4)) AS a(ID))
PRINT 'TRUE'
ELSE
PRINT 'FALSE';
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
-- PART 9
-- GREATER THAN OR EQUAL TO ANY AND LESS THAN OR EQUAL TO ANY
-- TRUE
-- 9 is greater than the MIN value in the comparison set AND
-- 9 is less than the MAX value in the comparison set
IF 9 >= ANY (SELECT ID FROM (VALUES(1),(2),(3),(10)) AS a(ID))
AND
9 <= ANY (SELECT ID FROM (VALUES(1),(2),(3),(10)) AS a(ID))
PRINT 'TRUE'
ELSE
PRINT 'FALSE';
----------------------------------------------------------------------
-- TRUE
-- >= ANY AND <= ANY equivalent using BETWEEN and the MIN/MAX functions
-- 9 is BETWEEN the MIN and MAX values in the comparison set
IF 9 BETWEEN (SELECT MIN(ID) FROM (VALUES(1),(2),(3),(10)) AS a(ID))
AND
(SELECT MAX(ID) FROM (VALUES(1),(2),(3),(10)) AS a(ID))
PRINT 'TRUE'
ELSE
PRINT 'FALSE';
----------------------------------------------------------------------
--THEN END---
Here is a good example set to practice using ANY and ALL on.
For the following tables, you need to calculate the commissions for each sales rep.
- Top Sellers
- Region Assignments
- Sales
Here you can start creating various commission payment schemes. I’ve capitalized ANY and ALL in the following mockup, but that doesn’t necessarily mean that ANY or ALL can be used to provide the answer.
- If the top selling products for the quarter are ALL from the same region, each sales rep gets a new car.
- If the top selling products are from different regions, ANY sales rep in that region gets a gift card.
- If ALL of the top selling products are from one sales rep, that sales rep wins a European vacation.
- If ANY sales rep is the only sales rep to sell that product, they win a free vacation day.
- etc….
Also, make sure to mockup you own test data to test different possibilities of input data.
DROP TABLE IF EXISTS #TopSellers;
DROP TABLE IF EXISTS #RegionAssignments;
DROP TABLE IF EXISTS #Sales;
GO
SELECT *
INTO #TopSellers
FROM (VALUES('Q1', 1, 'North', 'A'),
('Q1', 2, 'North', 'B'),
('Q1', 3, 'South', 'C')) AS a(Qtr,Rnk,Region,Product)
GO
SELECT *
INTO #RegionAssignments
FROM (VALUES('Joe', 'North'),
('Lee', 'North'),
('Sally', 'South'),
('Jan', 'East'),
('Tom', 'West'),
('Bob', 'West')) AS a(Name,Region)
GO
SELECT *
INTO #Sales
FROM (VALUES('Q1', 'Joe', 'North', 'A'),
('Q1', 'Joe', 'North', 'D'),
('Q1', 'Tom', 'West', 'A')) AS a(Qtr,Name,Region,Product);
GO