T-SQL中ANY、ALL和SOME是否常用？有无更优替代操作符？

Great question! I totally get where you're coming from—ANY and ALL do feel pretty obscure when you're learning T-SQL, and you rarely spot them in sample code or everyday queries. Let’s break this down clearly:

Are ANY/ALL rarely used? #

Yep, they’re definitely not part of the "go-to" toolkit for most T-SQL developers. The main reason is their syntax can feel counterintuitive, and there are almost always more readable, straightforward alternatives that achieve the exact same logic. Most teams prefer those alternatives because they make queries easier to debug and maintain for everyone on the team.

Better alternatives for ANY/ALL logic #

Let’s walk through common use cases and their more popular replacements:

For ANY operators #

• =ANY is identical to IN: This is the most common use of ANY, but IN is far more readable.

  -- Using =ANY (functional but less intuitive)
  SELECT CustomerID, CompanyName, Country
  FROM Customers
  WHERE Country =ANY (SELECT Country FROM Suppliers);
  
  -- Equivalent with IN (cleaner and widely used)
  SELECT CustomerID, CompanyName, Country
  FROM Customers
  WHERE Country IN (SELECT Country FROM Suppliers);
  

• >ANY/<ANY maps to comparing against MIN()/MAX(): >ANY means "greater than at least one value in the subquery"—which is the same as greater than the minimum value from that subquery.

  -- Using >ANY
  SELECT ProductID, ProductName, UnitPrice
  FROM Products
  WHERE UnitPrice >ANY (SELECT UnitPrice FROM Products WHERE CategoryID = 1);
  
  -- Equivalent with MIN()
  SELECT ProductID, ProductName, UnitPrice
  FROM Products
  WHERE UnitPrice > (SELECT MIN(UnitPrice) FROM Products WHERE CategoryID = 1);
  

For ALL operators #

• >ALL/<ALL maps to comparing against MAX()/MIN(): >ALL means "greater than every value in the subquery"—so it’s the same as greater than the maximum value from that subquery.

  -- Using >ALL
  SELECT ProductID, ProductName, UnitPrice
  FROM Products
  WHERE UnitPrice >ALL (SELECT UnitPrice FROM Products WHERE CategoryID = 1);
  
  -- Equivalent with MAX()
  SELECT ProductID, ProductName, UnitPrice
  FROM Products
  WHERE UnitPrice > (SELECT MAX(UnitPrice) FROM Products WHERE CategoryID = 1);
  

• =ALL is extremely rare: It requires a value to match every single result in the subquery. NOT EXISTS is almost always a better choice here because it’s more explicit.

  -- Using =ALL (hard to read and rarely used)
  SELECT CustomerID, CompanyName, Country
  FROM Customers c
  WHERE c.Country =ALL (SELECT Country FROM Suppliers);
  
  -- Equivalent with NOT EXISTS (clearer logic)
  SELECT CustomerID, CompanyName, Country
  FROM Customers c
  WHERE NOT EXISTS (
      SELECT 1 FROM Suppliers s
      WHERE s.Country <> c.Country
  );
  

Final thought #

While ANY/ALL are part of standard SQL, they’re not widely adopted in T-SQL because their alternatives are more readable and maintainable. That said, it’s still good to know how they work—you might run into them in older codebases or edge cases. For most day-to-day work, sticking with IN, EXISTS, or aggregate comparisons will make your queries easier for everyone to follow.

内容的提问来源于stack exchange，提问作者ulysses1904