CODEWITHSUNDEEP
sqloracle-databasejoin
louimat
louimat

Reputation: 27

Oracle SQL outer join on multiple columns -miscoding

According to http://www.orafaq.com/node/855 The record in that table for "Incomplete Join Trail"

Wrong way of coding is:

select *
from T1, T2, T3, T4
where T1.C1 = T2.C1(+)
and T2.C2 = T3.C2(+)
and T3.C3 = T4.C3;

Right way of coding is:

select *
from T1, T2, T3, T4
where T1.C1 = T2.C1(+)
and T2.C2 = T3.C2(+)
and T3.C3 = T4.C3(+);

This I couldn't understand. There is a possibility that I might want to do a equi-join T3 & T4 and outer join between the other tables. Why is the author saying it is incorrect?

According to http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5229892958977,

Tom is using the following example in his answer which seems similar to the "Incomplete Join Trail" example listed above,

select * 
  from t1,t2,t3
 where t1.x = t2.x and t2.y = t3.y(+);

Could anyone help me understand this?

Edit:

Please correct me if I am wrong. As per Allan's answer, the following code would be correct:

select *
from T1, T2, T3, T4
where T3.C3 = T4.C3
and T1.C1 = T2.C1(+)
and T2.C2 = T3.C2(+);

If the above is correct, will it be equivalent to the following quoted answer?.[ie., Can we do this in oracle's sql without the subquery in the quoted answer?]

SELECT *
FROM   t1, t2, (SELECT *
                FROM   t3, t4
                WHERE  t3.c3 = t4.c3) t3_4
WHERE  t1.c1 = t2.c1(+) AND t2.c2 = t3_4.c2(+);

Upvotes: 1

Views: 14384

Answers (1)

Allan
Allan

Reputation: 17429

Inner joins require a matching row to be present in order to return a row. In your first query, rows must be present in tables T1 and T4 in order for a resulting row to be returned. Since T1 connects to T4 by going through T2 and T3, those outer joins are render irrelevant. You can check this pretty easily, as the optimizer will know that they're irrelevant and will show them as inner joins in the explain plan.

You can still have an inner join between T3 and T4, but you need to do it in a sub-query in order to make the outer join with T2 functional:

SELECT *
FROM   t1, t2, (SELECT *
                FROM   t3, t4
                WHERE  t3.c3 = t4.c3) t3_4
WHERE  t1.c1 = t2.c1(+) AND t2.c2 = t3_4.c2(+);

Incidentally, SQL-99 syntax is preferred these days, which would allow you to do this without the sub-query:

SELECT *
FROM              t1
       LEFT JOIN  t2
               ON t1.c1 = t2.c1
       LEFT JOIN  (t3
       JOIN       t4
               ON t3.c3 = t4.c3)
               ON t2.c2 = t3.c2;

Finally, in the example you use from AskTom, the outer join is on the last join in the chain, not the middle. That case is okay.

Regarding the revised question, no the new query would not work. The where clause is not ordered, so the optimizer will treat that in exactly the same way as the original query. As far as I know, there's not any way to resolve this outside of using some form of sub-clause in the from clause.

Upvotes: 5

Related Questions