Processing outer join by oracle

[Rajesh99]

I have a number of tables they have some master/child realtionship i.e they can be joined by related columns. Now I understand I can create outer joins in these tables and should start with a MASTER table and from their keep joining tables to each other including ALL columns that are common.
Question is how does oracle process oracle outer joins made in this fashion. Does it start from top of where clause and keep creating internal tables as it goes down the tree of where clause or the order in which where clause is made does not matter, till it find another master, please see the example below and advise will this sql make sense to oracle.

e.g.
where master.pk = tab1.col(+) {col means a related column}
master.pk = tab2.col(+)
tab2.col = tab3.col(+) -- from here tab2 is a new master for chain below
tab2.col = tab4.col(+)
tab2.col = tab5.col(+)

Please help me to understand outer join processing.

 

[SantaMufasa]

Rajesh,

I'll bet that you haven't been able to get your code working yet, right? Oracle allows you to outer-join a table to, at most, one other table. In your example, you are trying (illegally) to outer join "tab2" to at least three other tables.

Next, Oracle doesn't really create internal temp tables, per se. The "order" of the table joining is, in the Oracle World, virtually irrelevant. The closest thing Oracle has to show you the "order" of the joining is a query's "Execution Plan", which you can obtain with Oracle's "explain plan" utility, and, I believe, a couple of other methods now.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Rajesh99]

Questions: Well you are right I get some results which look meaningless when I outer join table many times,
You can join only outer join table only once in sql? Is that right?

Also can if I do outer join followed by inner join what is first going to be excuted by sql e.g.
where master.pk = tab2.col(+)
tab2.col = tab3.col
If the order does not matter then how do I know what result is going to be produced.

 

[SantaMufasa]

Rajesh,

I absolutely guarantee you that your results will be the same regardless of the order that Oracle processes the joins, since both conditions must be true simultaneously for any records that appear in the result set.

Can you offer an example of a case that the results would differ if the join order changes?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Rajesh99]

I had A TABLE outer joined more than once, I got different results every time I changed order of outer join. But what you are saying it is illegal to outer join a table more than once? Do you have any oracle reference to prove this?
I have not read this anywhere in oracle manuals?

I have been doing sql for a long time and I have view it as if sql engine caches results as we traverse from top to bottom of where clause and keep joining data to stored cache. That is what I meant by temp tables i.e not a temp table but stored cache.

 

[SantaMufasa]

Here's an example of what I'm saying:

SQL> select * from x;

        ID
----------
         1
         2
         3
         4

SQL> select * from y;

        ID
----------
         2
         4

SQL> select * from z;

        ID
----------
         1
         3

select x.id, y.id, z.id
from x,y,z
where x.id = y.id(+)
  and z.id = y.id(+)
/
where x.id = y.id(+)
           *
ERROR at line 3:
ORA-01417: a table may be outer joined to at most one other table

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[MacLeod72]

Good morning-

Please forgive my ignorance as I was never good at the "TIE Fighter" outer join syntax but wouldn't that query be the same as the following, conceptually?

SQL> select * from X;

        ID
----------
         1
         2
         3
         4

SQL> select * from Y;

        ID
----------
         2
         4

SQL> select * from z;

        ID
----------
         1
         3

SQL> SELECT
  2      X.ID,
  3      Y.ID,
  4      Z.ID
  5  FROM
  6      X
  7  LEFT JOIN
  8      Y
  9  ON
 10      X.ID = Y.ID
 11  RIGHT JOIN
 12      Z
 13  ON
 14      Y.ID = Z.ID;

        ID         ID         ID
---------- ---------- ----------
                               3
                               1

Alternately, if you switch the last join so as to join X-> Y -> Z:

SQL>  SELECT
  2      X.ID,
  3      Y.ID,
  4      Z.ID
  5  FROM
  6      X
  7  LEFT JOIN
  8      Y
  9  ON
 10      X.ID = Y.ID
 11  LEFT JOIN
 12      Z
 13  ON
 14*     Y.ID = Z.ID
SQL> /

        ID         ID         ID
---------- ---------- ----------
         1
         3
         4          4
         2          2

Thanks!

--Rob

 

[Beilstwh]

Of course your going to get different results. A RIGHT JOIN is the reverse of the LEFT JOIN. The RIGHT JOIN returns all the rows from the second table , even if there are no matches in the first table. The LEFT JOIN returns all the rows from the first table , even if there are no matches in the second table.



Bill
Oracle DBA/Developer
New York State, USA