SQL Execution Order with multiple Joins

• david.watkins

  SSC Journeyman

  Points: 93

  More actions

  August 19, 2010 at 5:15 pm

  #1209421

  If you drop the 'AND d.y=c.y' condition from the last join, the results from the two queries are identical (as you would expect).

  Including it gives you different subsets of the pure left-joined result set.

• pankushmehta

  Ten Centuries

  Points: 1217

  More actions

  August 19, 2010 at 5:22 pm

  #1209424

  Shriji (8/17/2010)

  ---

  Thanks for the replies guys.

  While waiting on the reply, I created this small script and it proves what I assumed. The results are different:

  create table #a (x int, y int)

  create table #b (x int, y int)

  create table #c (x int, y int)

  create table #d (x int, y int)

  insert into #a values(1,2)

  insert into #a values(3,4)

  insert into #a values(5,6)

  insert into #a values(7,8)

  insert into #a values(9,10)

  insert into #b values(1,2)

  insert into #b values(3,4)

  insert into #b values(5,6)

  insert into #b values(11,12)

  insert into #c values(1,2)

  insert into #c values(3,4)

  insert into #c values(15,16)

  insert into #d values(13,14)

  insert into #d values(5,6)

  --Query 1

  select #a.*, #b.*, #c.*, #d.*

  FROM #a

  LEFT JOIN #b

  ON #a.x = #b.x

  LEFT JOIN #c

  ON #a.x = #c.x

  LEFT JOIN #d

  ON (#a.x = #d.x

  AND #d.y = #c.y)

  --Query 2

  select #a.*, #b.*, #c.*, #d.*

  FROM #a

  LEFT JOIN #b

  ON #a.x = #b.x

  LEFT JOIN #d

  ON #a.x = #d.x

  LEFT JOIN #c

  ON (#a.x = #c.x

  AND #c.y = #d.y)

  drop table #a

  drop table #b

  drop table #c

  drop table #d

  But still would like to know how does the engine determine order the processing Joins?

  Thanks

  The answers are different because the Join Conditions are different.

  First Query:

  Left Join C only has 1 Join condition, therefore results in 2 matching values(1,3) for "x".

  however for Left Join D, it has 2 Join conditions, and none of the rows match.

  therefore, it returns 2 valid rows for c and no valid rows for D.

  Second Query

  Left Join D only has 1 Join condition, therefore results in 1 matching value(5) for "x".

  however for Left Join C, it has 2 Join conditions, and none of the rows match.

  therefore, it returns 1 valid row for D and no valid rows for C.

  the posts here are correct, SQL will choose the best plan to execute the queries, irrespective of the position of the join. However, different join conditions will yeild different results.

  Hope this helps!