Left join between two tables using 3 columns and join the third table in Oracle

Question

I am using Oracle and I need to leave join 2 tables (which are actually the same table with an alias) based on 3 columns, and then join the 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 ', in' using statement or 'AND? Where exactly should the table_3 statement be inserted, even if it is not used in the left join?

Answer 1

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 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 junction. I personally prefer the ANSI SQL method. It seems cleaner to me. Your join predicates may not match my example, keep this in mind.