i am using Oracle and need to left join 2 tables (which are actually the same table with alias) based on 3 columns and then join with a third table. What should be the best syntax?
Select table_3.column_x From (table_1 left join table_2 Using (column_1 , column_2 , column_3)), table_3 Where Table_2.column_1 = table_3.column_1
Should I use ‘,’ on the ‘using statement or ‘AND’? Where exactly should I insert the table_3 statement even if it is not used on the left join?sqloraclejoinleft-join
Using ANSI SQL:
select * from TABLE1 "TABLE1" left join TABLE1 "TABLE2" on(TABLE1.c1 = TABLE2.C1 and TABLE1.c2 = TABLE2.C2) left join TABLE3 "TABLE3" on(TABLE1.c3 = TABLE3.c3)
Using Oracle Join Syntax you have:
select * from TABLE1 "TABLE1", TABLE1 "TABLE2", TABLE3 "TABLE3" where TABLE1.c1 = TABLE2.c1 (+) and TABLE1.c2 = TABLE2.c2 (+) and TABLE1.c3 = TABLE3.c3 (+)
(+) here represents the left join. I personally prefer ANSI SQL way. It seems cleaner to me. Your join predicates might not be the same with my example, keep that in mind.