Converting Subquery to a join

Question

I'm learning about joins in SQL and I am trying to create a subquery and convert it into a join so it does the same thing.

The subqueries I'm using is:

SELECT CustomerID
FROM Sales.SalesOrderHeader
where SalesOrderID IN (SELECT SalesOrderID
                       FROM Sales.SalesOrderDetail
                       WHERE ProductID IN (SELECT ProductID
                                           FROM Purchasing.PurchaseOrderDetail
                                           WHERE OrderQty >2));

So I essentially want to get the product ID's for order with a quantity over 2 from one table, feed this list into another table to get the salesorderID'S and then finally retrieve the customer id's.

I know I can do the same thing by creating a join, I had a go at this below:

SELECT CustomerID
FROM Sales.SalesOrderHeader AS soh, Sales.SalesOrderDetail as sod, Purchasing.PurchaseOrderDetail AS pod
WHERE soh.SalesOrderID = sod.SalesOrderID
AND sod.ProductID = pod.ProductID
AND pod.OrderQty >2;

I think this should retrieve the same data however when I run it I get more results from the join. I'm not understanding how these 2 queries are different from each other. Can anyone help? Thank you!

Answer 1

The IN condition in your first query means that only one matching row is returned for each parameter. Taking for example,

SELECT * FROM SomeTable WHERE Id IN (1, 1, 1, 1, 1, 2)

(Assuming SomeTable exists with an unique Id column and rows containing 1 and 2) would return only two results.

When you JOIN instead, you're combining the result sets from your query so you end up with the same data, but with some rows duplicated because (for example) a SalesOrderID value appears multiple times in the SalesOrderDetail table.

You can get around this by using a DISTINCT command in your query, that means you'll only get unique results, but for this case -- the subquery approach with IN probably represents your needs better.