Left join between 2 tables using 3 columns and joing to a third table on Oracle

4
votes

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?

1
sqloraclejoinleft-join

1 Answers

6
votes

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.