CODEWITHSUNDEEP
sql
user13993825
user13993825

Reputation: 21

Why SQL Join inside where exists don't work? I've two code snippets attached below

There are two tables, Suppliers and Products. I need to find SupplierName who has any product with price of product = 22.

Query #1:

SELECT SupplierName 
FROM Suppliers
WHERE EXISTS (SELECT SupplierName 
              FROM Suppliers
              INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID 
              WHERE Price = 22);

Query #2:

SELECT SupplierName 
FROM Suppliers
INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID
WHERE EXISTS (SELECT SupplierName 
              FROM Suppliers
              WHERE Price = 22);

The second snippet returns the correct result with 1 SupplierName, but the first one returns name of all suppliers (29).

Why? I know correct answer. I'm just curious why is the first code snippet returning all names when subquery (where exists part) in it is returning just one name?

Upvotes: 0

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

You want a correlated subquery:

SELECT s.SupplierName
FROM Suppliers s
WHERE EXISTS (SELECT 1
              FROM Products p
              WHERE s.SupplierID = p.SupplierID AND
--------------------^ correlation clause
                    p.Price = 22
             );

In your version, the subquery is not tied to the outer query -- the two references to Suppliers are different references (albeit to the same table). So, your version returns with all suppliers (if any have a product with a price of "22") or no suppliers (if none do).

You want the supplier tied to the product and that is what the correlation clause does.

Upvotes: 3

Related Questions