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