importance of order in the ON statement

[radmanmm]

Hello All,

I have had a question come up and was wondering if anyone knew the answer. Given the following query:

Select * From Table1 Left Outer Join Table2 On Table1.Id = Table2.Id

Is there any difference if the On statement was switched? Such as:

Select * From Table1 Left Outer Join Table2 On Table2.Id = Table1.Id

Thanks

[priyamv]

I think there is no difference between the two statements both will give the same results.But if you change the names of the tables after from statement then the result will change

[debasisdas]

Since it is Left Outer Join the position of the tables is very important.

A LEFT OUTER JOIN is one of the JOIN operations that allow you to specify a join clause. It preserves the unmatched rows from the first (left) table, joining them with a NULL row in the shape of the second (right) table.

[radmanmm]

Since it is Left Outer Join the position of the tables is very important.

A LEFT OUTER JOIN is one of the JOIN operations that allow you to specify a join clause. It preserves the unmatched rows from the first (left) table, joining them with a NULL row in the shape of the second (right) table.

So are you saying that the position of the tables are important specifically in the On statement?

table1.id = table2.id as opposed to table2.id = table1.id.

[Jim Doherty]

So are you saying that the position of the tables are important specifically in the On statement?

table1.id = table2.id as opposed to table2.id = table1.id.

If you look at the execution plan for the specific join the ON element with your table1.id=table 2.id you will see that their is no distinction on execution in terms of which way round it is presented. In other words it makes no difference a column is being compared with a column. The LEFT join preserve is of course on table1 as that is what you are selecting from.

Jim :)