Difference b/w putting condition in JOIN clause versus WHERE clause

Question

Suppose I have 3 tables.

Sales Rep

• Rep Code
• First Name
• Last Name
• Phone
• Email
• Sales Team

Orders

• Order Number
• Rep Code
• Customer Number
• Order Date
• Order Status

Customer

• Customer Number
• Name
• Address
• Phone Number

I want to get a detailed report of Sales for 2010. I would be doing a join. I am interested in knowing which of the following is more efficient and why ?

SELECT 
    O.OrderNum, R.Name, C.Name
FROM
    Order O INNER JOIN Rep R ON O.RepCode = R.RepCode
            INNER JOIN Customer C ON O.CustomerNumber = C.CustomerNumber
WHERE
    O.OrderDate >= '01/01/2010'

OR

SELECT 
    O.OrderNum, R.Name, C.Name
FROM
    Order O INNER JOIN Rep R ON (O.RepCode = R.RepCode AND O.OrderDate >= '01/01/2010')
            INNER JOIN Customer C ON O.CustomerNumber = C.CustomerNumber

Answer 1

JOINs must reflect the relationship aspect of your tables. WHERE clause, is a place where you filter records. I prefer the first one.

Make it readable first, table relationships should be obvious (by using JOINs), then profile

Efficiency-wise, the only way to know is to profile it, different database have different planner on executing the query

Wherein some database might apply filter first, then do the join subsquently; some database might join tables blindly first, then execute where clause later. Try to profile, on Postgres and MySQL use EXPLAIN SELECT ..., in SQL Server use Ctrl+K, with SQL Server you can see which of the two queries is faster relative to each other