Re: Joining tables in Oracle?

On Tue, 26 Dec 2000 23:36:26 -0000, "Gary" <garygfx_at_hotmail.com> wrote:

>How can I perform a LEFT join, INNER join, and a RIGHT join in Oracle 8i
>please? I've got some MS Access queries I need to convert to Oracles - it
>seems to be a little limited on the join syntax but I'm sure there's a way
>around it?
>
>For example, here are 2 queries I need to use with Oracle:
>
>SELECT ig_dictionary.*
>FROM ig_keywords
>INNER JOIN (ig_dictionary INNER JOIN ig_keydict ON
>ig_dictionary.dictionary_id = ig_keydict.dictionary_id) ON
>ig_keywords.keyword_id = ig_keydict.keyword_id
>WHERE ig_keywords.keyword_id='123456'
>
>SELECT ig_keywords.keyword_id, ig_keywords.keyword,
>ig_dictionary.dictionary_id, ig_dictionary.title
>FROM ig_keywords
>RIGHT JOIN (ig_dictionary LEFT JOIN ig_keydict ON
>ig_dictionary.dictionary_id = ig_keydict.dictionary_id) ON
>ig_keywords.keyword_id = ig_keydict.keyword_id
>WHERE ig_keywords.keyword_id='123456'
>
>Thanks in advance...
>Gary.
>
>
>

I posted this recently in reply to someone elses question. Maybe it will help.

Just went through this myself, I think I understand, so here goes:

LEFT JOIN and RIGHT JOIN are merely synonyms for an OUTER JOIN.

In Access the following statements are equal, or so I believe.

SELECT Moo.Id FROM Cow LEFT JOIN Moo ON Cow.Moo = Moo.Id; SELECT Moo.Id FROM Cow LEFT OUTER JOIN Moo ON Cow.Moo = Moo.Id;

The word LEFT or RIGHT merely specifies which table has the columns that are *not* referenced in the other table. So, in the above example, if table "Cow" has ids that are not in the "Moo" table, a NULL will show up as the result for that row (Moo.Id). This is because it is a LEFT join, and the table on the left of the join statement is "Cow". So LEFT or RIGHT specifies the table that has the extra rows.

An INNER JOIN, in Access can be specified as

SELECT Moo.Id FROM Cow INNER JOIN Moo ON Cow.Moo = Moo.Id;

• or -

SELECT Moo.Id FROM Cow, Moo WHERE Cow.Moo = Moo.Id;

These will render identical results.

Now Oracle can handle INNER JOINs in the latter method

SELECT Moo.Id FROM Cow, Moo WHERE Cow.Moo = Moo.Id;

To make it an OUTER Join you add the (+) symbol to the column of the table that is being referenced, that is the one that *must* have the values, or return a null. This is the exact *opposite* of Access.

SELECT Moo.Id FROM Cow, Moo WHERE Cow.Moo = Moo.Id(+);

In Access this is a LEFT JOIN. For a RIGHT JOIN:

SELECT Moo.Id FROM Cow, Moo WHERE Cow.Moo(+) = Moo.Id;

If I'm wrong, please tell me, I just dived into outer joins, for both Access and Oracle, yesterday...

Brian Received on Wed Dec 27 2000 - 14:15:06 CST