How do SQL EXISTS statements work?

Question

I'm trying to learn SQL and am having a hard time understanding EXISTS statements. I came across this quote about "exists" and don't understand something:

Using the exists operator, your subquery can return zero, one, or many rows, and the condition simply checks whether the subquery returned any rows. If you look at the select clause of the subquery, you will see that it consists of a single literal (1); since the condition in the containing query only needs to know how many rows have been returned, the actual data the subquery returned is irrelevant.

What I don't understand is how does the outer query know which row the subquery is checking? For example:

SELECT *   FROM suppliers  WHERE EXISTS (select *                  from orders                 where suppliers.supplier_id = orders.supplier_id); 

I understand that if the id from the supplier and orders table match, the subquery will return true and all the columns from the matching row in the suppliers' table will be outputted. What I don't get is how the subquery communicates which specific row (lets say the row with supplier id 25) should be printed if only a true or false is being returned.

It appears to me that there is no relationship between the outer query and the subquery.

Answer 1

Think of it this way:

For 'each' row from Suppliers, check if there 'exists' a row in the Order table that meets the condition Suppliers.supplier_id (this comes from Outer query current 'row') = Orders.supplier_id. When you find the first matching row, stop right there - the WHERE EXISTS has been satisfied.

The magic link between the outer query and the subquery lies in the fact that Supplier_id gets passed from the outer query to the subquery for each row evaluated.

Or, to put it another way, the subquery is executed for each table row of the outer query.

It is NOT like the subquery is executed on the whole and gets the 'true/false' and then tries to match this 'true/false' condition with outer query.

Answer 2

It appears to me that there is no relationship between the outer query and the subquery.

What do you think the WHERE clause inside the EXISTS example is doing? How do you come to that conclusion when the SUPPLIERS reference isn't in the FROM or JOIN clauses within the EXISTS clause?

EXISTS valuates for TRUE/FALSE, and exits as TRUE on the first match of the criteria -- this is why it can be faster than IN. Also be aware that the SELECT clause in an EXISTS is ignored - IE:

SELECT s.*   FROM SUPPLIERS s  WHERE EXISTS (SELECT 1/0                  FROM ORDERS o                 WHERE o.supplier_id = s.supplier_id) 

...should hit a division by zero error, but it won't. The WHERE clause is the most important piece of an EXISTS clause.

Also be aware that a JOIN is not a direct replacement for EXISTS, because there will be duplicate parent records if there's more than one child record associated to the parent.