Use the SQL ANY Operator with a subquery to compare one value to any value returned from the subquery. If one or more values from the SQL subquery satisfy the comparison’s condition, the comparison returns TRUE.
Unlike the ALL operator, to satisfy ANY, only one value must meet the condition.
Note: Keep and mind that SOME and ANY are equivalent. Wherever we use a SQL ANY operator in our examples, you are free to substitute SOME.
ANY Operator Syntax
Here is the general syntax for ANY:
SELECT column1, column2, … FROM table 1 WHERE column2 > ANY (subquery1)
Though we use greater than in our example above (>), when using SQL ANY you can substitute it for any valid comparison operator. Other examples include:
|X = ANY||Return TRUE if X equals ANY result returned from the subquery|
|X <> ANY||Return TRUE if X does not equal any result returned from the subquery.|
|X > Any||Return TRUE if X is greater than ANY result returned from the subquery.|
|X >= ANY||Return TRUE if X is greater than or equal to ANY result returned from the subquery.|
|X < ANY||Return TRUE if X is less than ANY result returned from the subquery.|
|X <= ANY||Return TRUE if X is less than or equal to ANY result returned from the subquery.|
SQL Any Operator Example
Here we are returning every product whose safety stock level is greater than any of the average safety stock levels for products grouped by DaysToManufacture.
Try it for yourself.
SELECT ProductID, Name, SafetyStockLevel, DaysToManufacture FROM Production.Product WHERE SafetyStockLevel > ANY (SELECT AVG(SafetyStockLevel) FROM Production.Product GROUP BY DaysToManufacture)
Keep in mind that for the result to return rows, any SafetyStockLevel from Product has to be greater than ANY of the subquery’s Average SafetyStockLevels.
To learn more about SQL ANY, check out these useful resources: