ANY and ALL: Comparing a Value to a Whole Subquery in T-SQL

ANY and ALL compare one value with a whole list from a subquery. One NULL or an empty list can flip the answer, so I never swap them for MIN or MAX without testing first.

A pricing question with a missing price

Say an analyst asks: “Which category 2 products cost more than every category 1 product?” Sounds easy. Then you notice one category 1 product has no price yet. Is it cheaper, dearer, or unknown? SQL Server says unknown, and that one word changes the result.

Let me build a tiny price table. Category 1 has prices 10, 20 and a NULL. Category 2 has 25 and 15. Run all blocks in one query window, since the table is a temp table.

SET NOCOUNT ON;
DROP TABLE IF EXISTS #Prices;
CREATE TABLE #Prices (Id int NOT NULL PRIMARY KEY, Category int NOT NULL,
                      Price decimal(12,2) NULL);
INSERT #Prices VALUES (1,1,10), (2,1,20), (3,1,NULL), (4,2,25), (5,2,15);

ALL says no, ANY says yes

Now ask the question twice. First with ALL, “greater than every category 1 price”. Then with ANY, “greater than at least one”.

SELECT Price FROM #Prices
WHERE Category = 2 AND Price > ALL(SELECT Price FROM #Prices WHERE Category = 1)
ORDER BY Id;

SELECT Price FROM #Prices
WHERE Category = 2 AND Price > ANY(SELECT Price FROM #Prices WHERE Category = 1)
ORDER BY Id;

ALL returns no rows, not even 25. ANY returns 25.00 and 15.00. Here is why. For 25, the comparisons are true, true and unknown. ALL needs every comparison to be true, so the unknown ruins it. ANY needs just one true, so it passes.

MIN and MAX quietly ignore the NULL

A common rewrite is “greater than the MAX” for ALL and “greater than the MIN” for ANY. But MAX and MIN skip NULL. So the rewrite answers a slightly different question.

SELECT Price FROM #Prices
WHERE Category = 2
  AND Price > (SELECT MAX(Price) FROM #Prices WHERE Category = 1)
ORDER BY Id;

SELECT Price FROM #Prices
WHERE Category = 2
  AND Price > (SELECT MIN(Price) FROM #Prices WHERE Category = 1)
ORDER BY Id;

The MAX query returns 25.00, while ALL returned nothing. The MIN query returns 25.00 and 15.00, which matches ANY here. So one rewrite changed the answer and one did not. Same table, different outcome. Only your business rule can tell you whether ignoring the missing price is acceptable.

What happens when the list is empty

Category 99 has no rows. This block turns each result into a word, so false and unknown cannot hide behind the same ELSE.

SELECT
    CASE WHEN 25 > ALL(SELECT Price FROM #Prices WHERE Category = 99)
         THEN 'true' ELSE 'false' END AS EmptyAll,
    CASE WHEN 25 > ANY(SELECT Price FROM #Prices WHERE Category = 99)
         THEN 'true' ELSE 'false' END AS EmptyAny,
    CASE WHEN 25 > (SELECT MAX(Price) FROM #Prices WHERE Category = 99)
         THEN 'true'
         WHEN NOT (25 > (SELECT MAX(Price) FROM #Prices WHERE Category = 99))
         THEN 'false' ELSE 'unknown' END AS EmptyMax,
    CASE WHEN CAST(NULL AS decimal(12,2))
         > ALL(SELECT Price FROM #Prices WHERE Category = 99)
         THEN 'true' ELSE 'false' END AS NullAgainstEmptyAll;

ALL over nothing is true, because there is no comparison that can fail. ANY over nothing is false, because there is no comparison that can succeed. MAX of nothing is NULL, so that comparison is unknown. Even a NULL value passes ALL over an empty list. Keep that in mind before you add an IS NOT NULL filter.

Ask for the violation directly

When the rule matters, I write it as a question about violations. “Is there any category 1 price that is missing or at least as high?” NOT EXISTS says that out loud. The same NULL trap also catches NOT IN, so I test that here too.

SELECT p.Price FROM #Prices AS p
WHERE p.Category = 2 AND p.Price IS NOT NULL
  AND NOT EXISTS
  (
      SELECT 1 FROM #Prices AS q
      WHERE q.Category = 1 AND (q.Price IS NULL OR p.Price <= q.Price)
  )
ORDER BY p.Id;

SELECT Price FROM #Prices
WHERE Category = 2 AND Price NOT IN (SELECT Price FROM #Prices WHERE Category = 1)
ORDER BY Id;

SELECT Price FROM #Prices AS p
WHERE Category = 2
  AND NOT EXISTS (SELECT 1 FROM #Prices AS q WHERE q.Category = 1 AND q.Price = p.Price)
ORDER BY Id;

The violation query returns nothing, just like ALL, because the missing price counts as a violation. NOT IN also returns nothing, since 25 compared with NULL is unknown. The NOT EXISTS form returns 25.00 and 15.00, because NULL never equals anything. Three similar-looking queries, two different answers.

Test the boundary cases yourself

Before you rewrite a quantified comparison, run it against three inputs: normal data, data with a NULL, and an empty set. Add a NULL on the outer side too, if the column allows it. Then pick the form that states the rule most clearly. Shorter syntax only helps once the meaning is right. When you are done, drop the temp table.

DROP TABLE IF EXISTS #Prices;

Write the missing-price rule in a comment, so the next person knows it was a choice.

A quantified comparison is not aggregate shorthand, it is a rule about a whole set.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.