3

Are these 2 queries equivalent in performance ?

select a.* 
from a 
inner join b 
    on a.bid = b.id 
inner join c 
    on b.cid = c.id 
where c.id = 'x'

and 

select a.* 
from c  
inner join b 
    on b.cid = c.id 
join a 
    on a.bid = b.id 
where c.id = 'x'

Does it join all the table first then filter the condition, or is the condition applied first to reduce the join ?

(I am using sql server)

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
jpprade
  • 3,497
  • 3
  • 45
  • 58
  • 3
    This could help: [Order Of Execution of the query](http://stackoverflow.com/questions/4596467/order-of-execution-of-the-query) – Pred Sep 11 '14 at 12:54
  • 1
    SQL server optimizer is generally smart enough to figure out the best sequence to use, so join table order is really not going to impact performance – Sparky Sep 11 '14 at 12:54

3 Answers3

1

The Query Optimizer will almost always filter table c first before joining c to the other two tables. You can verify this by looking into the execution plan and see how many rows are being taken by SQL Server from table c to participate in the join.

About join order: the Query Optimizer will pick a join order that it thinks will work best for your query. It could be a JOIN b JOIN (filtered c) or (filtered c) JOIN a JOIN b.

If you want to force a certain order, include a hint:

SELECT      *
FROM        a
INNER JOIN  b  ON ...
INNER JOIN  c  ON ...
WHERE       c.id = 'x'
OPTION (FORCE ORDER)

This will force SQL Server to do a join b join (filtered c). Standard warning: unless you see massive performance gain, most times it's better to leave the join order to the Query Optimizer.

Code Different
  • 90,614
  • 16
  • 144
  • 163
0

Read about http://www.bennadel.com/blog/70-sql-query-order-of-operations.htm

The execution order is FROM then WHERE, in this case or in any other cases I don't think the WHERE clause is executed before the JOINS .

CiucaS
  • 2,010
  • 5
  • 36
  • 63
0
select a.* 
from (select * from c where c.id = 'x') c
inner join b 
    on b.cid = c.id 
inner join a 
    on a.bid = b.id

This can create difference in execution.

Tasawar
  • 541
  • 4
  • 20