Subquery vs Traditional join with WHERE clause?

Question

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)

Answer 1

What about a third option?

SELECT ...
FROM Customers AS c
INNER JOIN Classification AS cf 
    ON cf.CustomerType = 'Standard' 
    AND c.TypeCode = cf.Code
INNER JOIN SalesReps AS s 
    ON cf.SalesRepID = s.SalesRepID

Personally, I prefer to use JOIN syntax to indicate the statements on which the overall set is defined, the foreign keys or other conditions that indicate two rows should be joined to make a row in the result set.

The WHERE clause contains the criteria which filter my result set. Arguably, this can become quite bloaty and complicated when you are performing a number of joins, however when you think in sets it follows a kind of logic:

• SELECT what columns I want.
• JOIN tables to define the set I want to get rows from.
• Filter out rows WHERE my criteria are not met.

By this logic, I'd always choose your second syntax for consistent readability.

Answer 2

The second clause is definitely more clear, and I suspect the optimizer will like it better too. And ideally you should specify the columns you need.