Oracle syntax left joins three or more tables

tyrone copex Source

I am trying to wrap my head around the old oracle Left Join syntax. It's fine with two tables:

FROM A, B
WHERE
A.Col = B.Col (+)

(Lets call this query, Q0)

This is easy enough to understand e.g. with this Venn diagram

But when we add a third table or more to the mix that's when my brain wants to shut down:

FROM A,B,C
WHERE 
A.C = B.C (+) AND
C.C = A.C (+)

(Lets call this Q1)

Which should be equal to: (I've just changed places on the left join conditions)

FROM A,B,C
WHERE 
C.C = A.C (+) AND
A.C = B.C (+)

(Lets call this one Q2)

So here is my take on these queries, my question here is if I have understood it correctly:

To my understanding a way to interpret left joins of more than 2 tables is to see it as a chain of left joins, where one is the input to the other, is this correct?

So in Q1 we have two left joins, the first is A Left Joined with B where A is the left table. The second left join is where it starts to get tricky for me. At first I thought it was table C left joined with table A where C was the left table. But this seem to be incorrect, rather it should be seen as C left joined with the result of the first left join, where C is the left table. Is this correct?

But how do I apply this "chain-principle" in Q2?

I tried copying the reasoning from Q1: the first join is a C left joined with A where C is the left table. The second join is a left join between A and the result of the first join, where A is the left table. But this doesn't make sense, the join condition of the second left join is between A and B columns, and there is no B column in the result from the first left join.

Out of curiosity+ I tried switching left and right tables in the second join, that is the result of the first join would instead act as the left table in the second left join, left joined with B. And it seems that this works, the result becomes the same as as Q1. But I have no idea how to explain why that works.

So if anyone could suggest a way of reasoning to apply generically for three or more tables when using the oracle syntax it would be greatly appreciated. It would also do if such a generic method consisted of a general way to translate an oracle syntax to ANSI syntax, since I can understand that much easier.

sqloraclejoinleft-join

Answers

answered 4 years ago Andrew not the Saint #1

(+) style outer joins are inferior in terms of functionality compared to standard ANSI OUTER JOINS (as well as readability IMHO), which Oracle supported since 9i.

What you want is effectively

FROM C
LEFT JOIN A ON C.C = A.C
LEFT JOIN B ON A.C = B.C

Does it make sense now? To be honest, I don't quite understand your questions because how the above syntax works is really obvious to me...

answered 4 years ago talek #2

You can see it in a cascading way. However, the key is to look for those tables which are left and right joined within the same query. In this case, the order is different: the condition where the table is right joined is applied first. I hope the following diagram will shed some light on this:

enter image description here

You can also check the order of these joins by looking at the execution plan of the query:

For Q1:

select a.c a, b.c b, c.c c   from a, b, c  where a.c = b.c (+)    and
c.c = a.c (+)

------------------------------------------------------------------------
| Id  | Operation           | Name | E-Rows |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |        |       |       |          |
|*  1 |  HASH JOIN OUTER    |      |      4 |  2168K|  2168K|  805K (0)|
|*  2 |   HASH JOIN OUTER   |      |      4 |  2616K|  2616K|  981K (0)|
|   3 |    TABLE ACCESS FULL| C    |      4 |       |       |          |
|   4 |    TABLE ACCESS FULL| A    |      4 |       |       |          |
|   5 |   TABLE ACCESS FULL | B    |      4 |       |       |          |
------------------------------------------------------------------------

For Q2:

select a.c a, b.c b, c.c c   from a, b, c  where c.c = a.c (+)    and
a.c = b.c (+)

------------------------------------------------------------------------
| Id  | Operation           | Name | E-Rows |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |        |       |       |          |
|*  1 |  HASH JOIN OUTER    |      |      4 |  2168K|  2168K|  801K (0)|
|*  2 |   HASH JOIN OUTER   |      |      4 |  2616K|  2616K|  983K (0)|
|   3 |    TABLE ACCESS FULL| C    |      4 |       |       |          |
|   4 |    TABLE ACCESS FULL| A    |      4 |       |       |          |
|   5 |   TABLE ACCESS FULL | B    |      4 |       |       |          |
------------------------------------------------------------------------

comments powered by Disqus