ANY, ALL, and SOME equivalents

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 various ways, such as with subqueries, to filter and retrieve specific data from a table. It’s important to remember 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 extra thinking.

I have broken down the most relevant forms for this blog to understand 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 nine operations and their equivalent statements. If you understand these nine, 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	`= ANY	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.

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 your 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