Subquery vs Traditional join with WHERE clause?
Posted
by BradC
on Stack Overflow
See other posts from Stack Overflow
or by BradC
Published on 2009-08-13T13:31:23Z
Indexed on
2010/05/27
17:01 UTC
Read the original article
Hit count: 173
sql
|sql-server
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)
© Stack Overflow or respective owner