Question 21086 in Mysql Question posted in
The official MySQL documentation can be found here.

Why does this query return values? – Mysql

I am starting to learn SQL and I had a question.
I am running the following query:

SELECT ProductName 
FROM Products
WHERE ProductID =ALL (
       SELECT ProductID 
       FROM OrderDetails 
       WHERE Quantity < 0
)
order by ProductID;

Why does this query return values if I am looking for quantity less than zero?
In the OrderDetails table, there are no quantity less than zero. So shouldn’t the output of this query be empty?
Is there something I am missing.

w3schools SQL Practice

Tags:

2

Answers


  1. 0

    Is it any clearer written as

    SELECT p.* 
FROM Products p
WHERE EXISTS (
    SELECT NULL 
    FROM OrderDetails o
    WHERE o.ProductID = p.ProductId
        AND o.Quantity = 10
 );

    I.e., find all products where an order has been placed for exactly 10 of them.

    Login or Signup to reply.
  2. 0

    If your subquery has a WHERE clause that is always false, then the subquery returns an empty result.

    https://dev.mysql.com/doc/refman/8.0/en/all-subqueries.html says:

    Finally, the expression is TRUE if table t2 is empty. So, the
    following expression is TRUE when table t2 is empty:

    SELECT * FROM t1 WHERE 1 > ALL (SELECT s1 FROM t2);

    Demo: https://dbfiddle.uk/zCO71Q15

    An ALL predicate means it’s false if at least one row of the subquery causes the comparison to be false.

    But if the subquery returns zero rows, then of course there can’t be any rows that cause the predicate to be false.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search