CODEWITHSUNDEEP
sqlinner-join
NOMO
NOMO

Reputation: 155

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

Suppose I have 3 tables.

Sales Rep

Orders

Customer

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

Upvotes: 3

Views: 754

Answers (1)

Michael Buen
Michael Buen

Reputation: 39393

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

Upvotes: 4

Related Questions