joins in oracle 9i

[hisselt]

Can somebody tell me how many joins Oracle can handle?

I am curious because nobody in my neigbourhood seems to know.

Thanks

 

[WantToBeDBA]

Oracle can handle following joins :

1) Equi-joins : The joins which are made by comparing the common column value

2) Self-Join : The join made to the same table using aliases, i.e., mock names to identify "nearly duplicate" values in the same table.

3) Outer Join : The jois made by joining two or more tables the result of which inludes the relating records and the non-rlated records for the corrosponding equi-joins. The values not matching in the where clause are also displayed. This can be done using + operator. Two types - left outer join and Right outer join.

4) Cross Join : the cartesian product. Each row of the first column is multiplied with each row of the other tables producing huge list of records.

You can refer to any good oracle book to get detail idea..

Hope this gives you at least the starting point to ponder more upon.

Regards.

 

[ytorres]

Hi!

We are wprking with a script and we are using sub selects (a select withing a main select) and the outer joins in this sub select are not working properly... It is still ignoring some records that should be included in the output if the outer joins worked properly.

Do you know if there is another way of working with outer joins on Oracle 9i??, these worked fine on the previous Oracle version we were using...

Heeelp!!!

Y

 

[SantaMufasa]

Hisselt,

If you were wondering about the maximum number of tables that Oracle can join versus the number of types of joins Oracle can process, then the answer I would offer is "unknown". On a Tek-Tips thread from some months ago, I successfully executed a join of > 1024 tables. So, unless you are exceeding that number, I believe you are safe.

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:45 (02Sep04) UTC (aka "GMT" and "Zulu"), 13:45 (02Sep04) Mountain Time)

 

[ytorres]

Thanks for your reply...

I think I didnt explain the problem too well. (sorry...)

Anyway, the problem we were having is that we were using on Oracle 9i the old way of writing outer joins (+) and now its completely different.

I did found a thread here with an example but its a bit too simple and we still have doubts as to how to properly write the code.

We need to make outer joins of several fields that are in the same 2 tables.

We already included on the FROM the RIGHT OUTER JOIN wording and the correct sintax there, then on the ON part we wrote the 2 first fields that will be comparing in the tables. But we have more fields that need to be included in this outer join. Do we just add them by writing AND and each of the fields = fieldb so on and so forth or are we doing it wrong?

Thanks again for your help.

 

[SantaMufasa]

YTorres,

It seems like you hijacked "Hisselt's" thread for your use. You are certainly worthy enough to create your own thread, which appears to be a different issue from your issue. If you would like to start a new thread for your problem, please include the code you are using for your outer join.

Hisselt, how are we doing at addressing your question?

Mufasa
(aka Dave of Sandy, Utah, USA @ 00:14 (03Sep04) UTC (aka "GMT" and "Zulu"), 17:14 (02Sep04) Mountain Time)