How do SQL EXISTS statements work?

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.

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.

You can produce identical results using either JOIN, EXISTS, IN, or INTERSECT:

SELECT s.supplier_id
FROM suppliers s
INNER JOIN (SELECT DISTINCT o.supplier_id FROM orders o) o
    ON o.supplier_id = s.supplier_id

SELECT s.supplier_id
FROM suppliers s
WHERE EXISTS (SELECT * FROM orders o WHERE o.supplier_id = s.supplier_id)

SELECT s.supplier_id 
FROM suppliers s 
WHERE s.supplier_id IN (SELECT o.supplier_id FROM orders o)

SELECT s.supplier_id
FROM suppliers s
INTERSECT
SELECT o.supplier_id
FROM orders o

If you had a where clause that looked like this:

WHERE id in (25,26,27) -- and so on

you can easily understand why some rows are returned and some are not.

When the where clause is like this:

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

it just means : return rows that have an existing record in the orders table with te same id.

How do SQL EXISTS statements work?

Related

Recent Posts