CODEWITHSUNDEEP
mysqlsql
Nicholas Hassan
Nicholas Hassan

Reputation: 969

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 OrderDetails WHERE Quantity = 10);

I interpreted this as "Return the product name if every instance of its product ID has a quantity of 10 in Order Details." It returns 0 records, because every product had at least one order where it was ordered in a quantity other than 10.

So I wanted to see a positive example. I found that the max quantity of any item in a shipment was 120.

SELECT Max(Quantity) FROM OrderDetails;"

I then modified the original statement to say:

SELECT ProductName 
FROM Products
WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity < 1000);

So here I thought I was trying to say "Return the product name if every instance of its product ID has a quantity of less than 1000 in Order Details," which would surely be every product, since the max quantity was 120. But I still got 0 records. Can anyone explain what was wrong with my syntax/interpretation?

The SQL simulator that's linked to the tables can be found here: https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all2&ss=-1

Upvotes: 0

Views: 1663

Answers (2)

Caleth
Caleth

Reputation: 62636

= 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

Upvotes: 1

Radim Bača
Radim Bača

Reputation: 10701

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
WHERE 10 = 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
WHERE NOT EXISTS(SELECT 1
                FROM OrderDetails 
                WHERE OrderDetails.ProductID  = Products.ProductID AND
                      OrderDetails.Quantity != 10);

Upvotes: 2

Related Questions