Subquery vs Traditional join with WHERE clause?
- by BradC
When joining to a subset of a table, any reason to prefer one of these formats over the other?
Subquery version:
SELECT ...
FROM Customers AS c
INNER JOIN (SELECT * FROM Classification WHERE CustomerType = 'Standard') AS cf
ON c.TypeCode = cf.Code
INNER JOIN SalesReps s ON cf.SalesRepID = s.SalesRepID
vs the WHERE clause at the end:
SELECT ...
FROM Customers AS c
INNER JOIN Classification AS cf ON c.TypeCode = cf.Code
INNER JOIN SalesReps AS s ON cf.SalesRepID = s.SalesRepID
WHERE cf.CustomerType = 'Standard'
The WHERE clause at the end feels more "traditional", but the first is arguably more clear, especially as the joins get increasingly complex.
Only other reason I can think of to prefer the second is that the "SELECT *" on the first might be returning columns that aren't used later (In this case, I'd probably only need to return cf.Code and Cf.SalesRepID)
