MySQL - if EXISTS returns true, shouldn't the outer query results be no more filtered

Question

I am quoting from here :

"You can use the query above as a correlated subquery to find customers who placed at least one sales order with the total value greater than 60K by using the EXISTS operator:

SELECT 
    customerNumber, 
    customerName
FROM
    customers
WHERE
    EXISTS( SELECT 
            orderNumber, SUM(priceEach * quantityOrdered)
        FROM
            orderdetails
                INNER JOIN
            orders USING (orderNumber)
        WHERE
            customerNumber = customers.customerNumber
        GROUP BY orderNumber
        HAVING SUM(priceEach * quantityOrdered) > 60000);

" You can paste the code in the editor here to see the results.

Suppose I omit the WHERE clause in the subquery and the EXISTS operator returns TRUE. 1) Then why can't I get the customerNumber and customerName of all the customers ? 2) How many times is the EXISTS operator evaluated ?

1) Doesen't "SELECT customerNumber, customerName FROM customers" work? It should. That would be the query, if EXISTS returned TRUE. — JochenJung, Oct 26 '17 at 12:57
1) If you ommit where in the subquery you will get all customers from customers table only if total value for all customers is > 60k in orders table, i.e. if your subquery returns at least one row on its own. — dkasipovic, Oct 26 '17 at 13:09

score 1 · Answer 1 · answered Oct 26 '17 at 13:24

1

When you remove the WHERE clause in your subquery, you will only get all customerNumbers and -names, if there's any orderNumber where priceEach * quantityOrdered > 60000. If there's not, no row will be returned.

Regarding your second question, I'm not sure what you mean. The specialty of WHERE EXISTS (SELECT whatever FROM foo is, that EXISTS opposed to WHERE x IN (SELECT y FROM foo) stops as soon as the subquery returns a boolean true or a value other than 0. The IN (<subquery>) doesn't stop after the first hit. Is that what you're asking?

answered Oct 26 '17 at 13:24

fancyPants

50,732
33
89
96

`EXISTS opposed to WHERE x IN (SELECT y FROM foo) stops as soon as the subquery returns a boolean true or a value` - if that is so, then when I do not omit the `WHERE` clause from the sub query why doesn't the result show all the customers ? – Istiaque Ahmed Oct 26 '17 at 13:46
Because not for every customerNumber a result is returned. You have a bunch of customerNumbers from the outer query. Each customerNumber is used in a separate execution of the subquery. Then there are following possibilities. 1) There is no entry in the orderDetails table with a customerNumber from the outer query. No result is returned, the outer query doesn't print a customerName to your terminal. 2) There is a customerNumber with an entry in orderDetail, but there is no order with a value over 60000. No result is returned from the subquery, no customerName from the outer query is returned. – fancyPants Oct 26 '17 at 13:54
3) There is a customerNumber with an orderDetail entry with value over 60000. Then it actually doesn't matter what you select in the subquery, as long as it's not false or 0. You can for example simply `select 1 from orderDetails...having...`. So when the subquery returns a true or non-0 result, the outer query returns this customerName. – fancyPants Oct 26 '17 at 13:56
@IstiaqueAhmed So, did I explain good enough? Any more questions? – fancyPants Oct 27 '17 at 11:22

Harshil Doshi · Accepted Answer · 2017-10-26T13:36:45.247

-1

1) If you remove where clause from subquery, it is giving all customername and customernumbers because currently you've at least 1 order who qualifies the following condition:

HAVING SUM(priceEach * quantityOrdered) > 60000)

Hence, the subquery will always return True.

But when there are no orders that qualifies the above condition, it won't display anything as subquery will always return False.

As for now, subquery returns true and displaying all customers. You can verify that as follows:

SELECT 
    count(*)
FROM
    customers
WHERE
    EXISTS( SELECT 
            orderNumber, SUM(priceEach * quantityOrdered)
        FROM
            orderdetails
                INNER JOIN
            orders USING (orderNumber)       
        GROUP BY orderNumber
        HAVING SUM(priceEach * quantityOrdered) > 60000);

Result:

count(*)
---------
122

Now, run this query:

Select count(*) from customers;

Result:

count(*)
---------
122

Now, both returns same amount of rows hence you can tell that it subquery returns true for all customers.

2) I don't think there is a definite or exact answer for that. Normally, Query planner/Query optimizer transform co-related subqueries with EXISTS into Join and hence they executed only once.

For more info, click here

Hope it helps!

edited Oct 26 '17 at 13:36

answered Oct 26 '17 at 13:11

Harshil Doshi

3,497
3
14
37

Your answer is wrong. Or rather imprecise. And your "proof" is kind of poor. Just because by coincidence the right result is returned, doesn't mean it's always right. – fancyPants Oct 26 '17 at 13:25
It's not coincidence. I checked subquery without where clause and it returns rows(3 rows to be precise). It means, it'll return true for each and every customer and hence return all the customers. What's wrong in this logic. – Harshil Doshi Oct 26 '17 at 13:27
I went for `count(*)` because customernumber are not sequential. Hence, that's the only way to compare the results. – Harshil Doshi Oct 26 '17 at 13:29
Yes, now that you've basically copied my answer it is right. – fancyPants Oct 26 '17 at 13:41
Copy? I already explain the added points to you in comments above. I just forgot to add that in my answer. Thanx for pointing that out. But copy?? No way. – Harshil Doshi Oct 26 '17 at 13:45
Thank you for mark it as answer. Fell free to ask doubts if you've any. – Harshil Doshi Nov 06 '17 at 15:52

MySQL - if EXISTS returns true, shouldn't the outer query results be no more filtered

2 Answers2