Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: How to convert Access queries into Oracle

Re: How to convert Access queries into Oracle

From: Brian Tkatch <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK>
Date: Fri, 01 Dec 2000 14:42:05 GMT
Message-ID: <3a27b4df.913833515@news.alt.net>

On 30 Nov 2000 10:12:19 +0100, ivanovic27_at_icqmail.com wrote:

>Hello everybody
>Can somebody help me??
>I have to convert some Access queries that use Left Join and Right Join into Oracle and I don't know how to do it.
>

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;

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 Fri Dec 01 2000 - 08:42:05 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US