SQL any and all keyword

Question

Rewriting some queries and was wondering what would be the use case for ANY and ALL keyword?

Answer 1

Hi @Jonathan Brotto
As long as we use one of the SQL comparison operators (=, <, >, <=, >=) to compare the values of two expressions, the expressions before and after the operator must be a single value.
The keywords SOME, ANY, ALL allow the use of comparison operators to compare a single value with the result returned by a subquery, where the result returned by the subquery can be multiple rows.
Basic syntax: scalar_expression { = | <> | != | > | >= | !> | < | <= | !< } All | Any |Some ( subquery ) .
The keyword SOME and the keyword ANY have the exact same function
In fact, subqueries implemented by aggregate functions are usually more efficient than direct ANY or All queries. ANY and ALL are related to aggregate functions as follows:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

That is a very good question! In fact, I have never in my life with SQL ever used any of them. I believe that you can always say the same thing with EXISTS / NOT EXISTS.

Or take this example in Books Online:

-- Uses AdventureWorks  

CREATE PROCEDURE DaysToBuild @OrderID INT, @NumberOfDays INT  
AS  
IF   
@NumberOfDays >= ALL  
   (  
    SELECT DaysToManufacture  
    FROM Sales.SalesOrderDetail  
    JOIN Production.Product   
    ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID   
    WHERE SalesOrderID = @OrderID  
   )  
PRINT 'All items for this order can be manufactured in specified number of days or less.'  
ELSE   
PRINT 'Some items for this order can''t be manufactured in specified number of days or less.' ;

Certainly this could be changed to:

IF @NumberOfDays >=
(
SELECT MAX(DaysToManufacture )

Answer 3

Just wondering as I could use different approaches to get similar results with EXISTS and/or correlated queries. Just wondering does any, all, or some keyword affect the execution path/performance?