3

When joining tables with either the ANSI-89 (old) or the ANSI-92 ("new") method of joining tables, does it matter which side you place the fields from the 2 joining tables.

For example, is it better to do:

From 
TABLE_1 A

Join
TABLE_2 B
    on A.ID = B.ID

Or is the following better?

    on B.ID = A.ID

Is it simply aesthetics? Or does it effect how the joins work?

EDIT: For further clarification, what about Left Joins? For example:

From
TABLE_1 A

Left Join
TABLE_2 B
    on A.ID = B.ID

Is this the same as 

    on B.ID = A.ID

However, if using ANSI-89 Where A.ID = B.ID (+) is NOT the same as Where B.ID = A.ID (+) since the second joins A ONTO B?

Phillip
  • 447
  • 1
  • 4
  • 12
  • The `(+)` is supposed to be applied to the column that is on the outer-joined table, so it should be `A.ID = B.ID(+)` vs. `B.ID(+) = A.ID`. And yes, it doesn't matter which way you put them. – Jeffrey Kemp Nov 11 '13 at 05:24

2 Answers2

3

It makes no difference. The only time the order matters is when you are doing LEFT and RIGHT OUTER joins, but those keywords all fall before the ON keyword.

NL - Apologize to Monica
  • 493
  • 11
  • 17
  • I'll update the original question, but if you have a Left Join, and do `B.ID = A.ID`, are you saying it still works the same as `A.ID = B.ID` in ANSI-92? But with ANSI-89 it DOES matter the order (ie `A.ID = B.ID (+)` is not the same as `B.ID = A.ID (+)` since it indicates you are joining A ONTO B, instead of B ONTO A? – Phillip Nov 08 '13 at 21:42
  • Yes, in every case, the A.ID = B.ID is interchangeable with B.ID = A.ID, there is nothing beyond aesthetics in that choice. I probably shouldn't have mentioned LEFT JOIN. FROM Table_1 A LEFT OUTER JOIN Table_2 B will be different than FROM Table_2 B LEFT OUTER JOIN Table_1 A, but that's unrelated to your question so I should have omitted it. – NL - Apologize to Monica Nov 08 '13 at 21:55
1

The = operator is symmetric, so a.id = b.id is exactly the same as b.id = a.id. Personally, I prefer having the fields from the driving table (the one in the FROM clause) on the left hand side of the operator, but that's purely an aesthetic preference.

Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • I would normally agree, it's looks better when using ANSI-89. However, when using ANSI-92, I find that, especially when my From becomes `T1 join T2 on T2.ID = T1.ID and T2.TYPE = 'X' and T2.DATE = sysdate` etc, it becomes easier to recognize what table is being used. – Phillip Nov 08 '13 at 21:49