Why Doesn't My All Operator Work In This Sql Statement?

I'm going through W3school's SQL tutorial, and they have an example here with the ALL operator. SELECT ProductName FROM Products WHERE ProductID = ALL (SELECT ProductID FROM Order

Solution 1:

Your query returns a product if all instances sold in quantity equal to 10 are this particular product. In other words, the product would be in the result only if there is no other product sold in quantity equal to 10. To return what you need you have to change it slightly:

SELECT ProductName 
FROM Products p
WHERE10=ALL (SELECT Quantity 
                FROM OrderDetails 
                WHERE OrderDetails.ProductID  = Products.ProductID  );

There are also other ways to solve it using NOT EXISTS or NOT IN.

SELECT ProductName 
FROM Products p
WHERENOTEXISTS(SELECT1FROM OrderDetails 
                WHERE OrderDetails.ProductID  = Products.ProductID AND
                      OrderDetails.Quantity !=10);

Solution 2:

= All doesn't mean what you think it means. column = All(subquery) is equivalent to NOT( column NOT IN (subquery) ) AND COUNT(subquery) > 0

The left hand side is a single value, the right hand side is a result set. It is true when the single value is equal to every value in the result set, and the result set is non-empty.

Your subquery SELECT ProductID FROM OrderDetails WHERE Quantity < 1000 returns more than 1 unique value, so an = All expression with it on the right hand side is never true. Because SELECT ProductID FROM OrderDetails WHERE Quantity = 10 has only one result, it is true for ProductID 42, which is not in the example Products