0

i have the following sql query:

select d.c3
from table1 a,
     table2 b,
     table3 e,
     table4 d
where a.c1(+) = b.c1 AND d.c1(+) = e.c1 and e.c2 (+) = b.c2

I would like to change the plus operator (+) to a left join or any other join operator.

Any help please?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Fleur.T
  • 1
  • 1
    So what's the problem? – HoneyBadger Jun 11 '18 at 09:56
  • Great idea! Show us what you've tried! – jarlh Jun 11 '18 at 09:57
  • how do i transform this query since i have multiple joins on same tables? – Fleur.T Jun 11 '18 at 09:58
  • i tried: select d.c3 from table1 a left outer join table2 b on a.c1=b.c1, table4 d left outer join table3 e on d.c1 = e.c1, table3 e2 left outer join table2 b2 on e2.c2 = b2.c2 where e2.id = e.id and b2.id = b.id; but it didn't work :( – Fleur.T Jun 11 '18 at 10:05
  • I believe it is: `SELECT d.c3 FROM table2 b LEFT OUTER JOIN table1 a ON ( a.c1 = b.c1 ) LEFT OUTER JOIN table3 e ON ( b.c2 = e.c2 ) LEFT OUTER JOIN table4 d ON ( e.c1 = d.c1 )` – MT0 Jun 11 '18 at 10:07
  • it didn't work too @MT0 – Fleur.T Jun 11 '18 at 10:30
  • The explain plan shows that they are identical. Compare `EXPLAIN PLAN FOR select d.c3 from table1 a, table2 b, table3 e, table4 d where a.c1(+) = b.c1 AND d.c1(+) = e.c1 and e.c2 (+) = b.c2; select * from table(dbms_xplan.display('plan_table',null,'typical'));` to `EXPLAIN PLAN FOR select d.c3 from table2 b LEFT OUTER JOIN table1 a ON (b.c1 = a.c1) LEFT OUTER JOIN table3 e ON (e.c2 = b.c2) LEFT OUTER JOIN table4 d ON ( e.c1 = d.c1 ); select * from table(dbms_xplan.display('plan_table',null,'typical'));` and they are the same. – MT0 Jun 11 '18 at 10:54
  • but the first query returned 1 lign while the second one did'nt return any lign...@MT0 – Fleur.T Jun 11 '18 at 12:27
  • When you reply to someone's solution, it isn't helpful to only state it "didn't work". There are hundreds of reasons a query can be considered "not working". Do you get an error, if so what is the error. Do you get unexpected results, if so provide an [mcve]. Also, code in a comment is very hard to read, if you want to provide code or data please edit your question to include it. Help us help you! – HoneyBadger Jun 11 '18 at 15:48

0 Answers0