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: 151

Filed under:
|

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

Related posts about sql

Related posts about sql-server