Re: MS/ACCESS SQL -> ORACLE SQL

Here's something I posted a while back. Maybe it'll help.

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;

Brian

On Sat, 29 Sep 2001 13:47:54 +0100, "Peter Sulikowski" <anon_at_anon.com> wrote:

>Hi - I'm migrating a piece of software that uses MS/Access/ADO in VB using
>embedded SQL to Oracle and I can't make head nor tails of Microsofts SQL
>(well the LEFT and JOIN statements anyway). Theres are absolutely dozens of
>SQL statements in the Visual Basic sourcecode that look like this. Would it
>be possible for one of you guru's to put this in Oracle SQL format for me so
>I can see what you've done and thus what I'll have to do for the translation
>for the remainder of all the SQL. I've tried to read up on the LEFT and JOIN
>statements onl-line but just don't seem to be getting the same hitset back.
>I'm using Oracle 8i. Here's a piece of the SQL from the MS/Access system.
>Thanks a lot in advance.
>Pete.
>
>
>SELECT Customers.CustomerNumber, Transactions.TransactionID,
>Transactions.DateCreated, Transactions.Date_Completed,
>Ammendments.AmmendmentType1, Ammendments.AmmendmentType2,
>Discounts.Discount1, Discounts.Discount2, CustomerArea.AreaNumber,
>Discounts.DiscountPriority AS Discounts_DiscountPriority_Order,
>Ammendments.AmmendmentID, CustomerArea.AreaNumber1,
>CustomerArea.AreaNumber2, CustomerArea.SalesmanNo,
>Transactions.TransactionNumber, Transactions.TransactionType FROM (Customers
>LEFT JOIN ((Transactions LEFT JOIN Ammendments ON Transactions.TransactionID
>= 502) LEFT JOIN (Discounts LEFT JOIN MessageNotes ON Discounts.DiscountID =
>MessageNotes.MessageID) ON Ammendments.AmmendmentID = Discounts.DiscountID)
>ON Customers.ID = Transactions.IDType) LEFT JOIN CustomerArea ON
>MessageNotes.MessageID = CustomerArea.AreaNumber WHERE
>(((Customers.CustomerNumber)=100)) ORDER BY Customers.Transaction_Type,
>Transactions.TransactionID, Ammendments.AmmendmentType1,
>Ammendments.AmmendmentType2, Discounts.Discount1, Discounts.Discount2,
>CustomerArea.AreaNumber, Discounts.DiscountPriority,
>Ammendments.AmmendmentID, Discounts.DiscountPriority,
>Ammendments.AmmendmentID
>
>
Received on Sun Sep 30 2001 - 07:41:59 CDT