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 ?

Answer 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!