# Translating an outer-join-ridden SQL statement from Informix to Oracle **URL:** \ **Category:** Factual Questions\ **Created:** [August 21, 2004, 6:07pm UTC](https://boards.straightdope.com/t/translating-an-outer-join-ridden-sql-statement-from-informix-to-oracle/260866 "2004-08-21T18:07:22Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![bordelond](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bordelond/32/150_2.png) [@bordelond](https://boards.straightdope.com/u/bordelond)\ **Post date:** [August 21, 2004, 6:07pm UTC](https://boards.straightdope.com/t/translating-an-outer-join-ridden-sql-statement-from-informix-to-oracle/260866/1 "2004-08-21T18:07:22Z")
Here is a portion of an Informix SQL statement I am being asked to translate for use with an Oracle 9i database: FROM SVCORD\_LOC\_SVC ,NC\_REF\_DEC ,OUTER (SVCORD\_SYSTEM ,OUTER (NSPS\_SYSTEM ,OUTER PROVIDER PROV1)) ,OUTER (SVCORD\_CIRCUIT ,OUTER (CIRCUIT ,OUTER PROVIDER)) ,OUTER (LOCATION ,OUTER ADDRESS) ,OUTER OFFNET\_DESC ,OUTER (CUSTOMER ,OUTER Contact) ,OUTER SERVICE\_ADDRESS ,MACD\_SVCORD ,OUTER (MACD\_WORKORDER ,OUTER (CUSTOMERACCOUNT ,OUTER BILLINGACCOUNT)) Now then, Oracle 9i can use outer-join syntax in the FROM clause (previous Oracle versions could not). But the Oracle 9i outer-join syntax seems to require that one knows whether or not a left or right outer join is required – the three options are RIGHT OUTER JOIN, LEFT OUTER JOIN, and FULL OUTER JOIN. Is there some way I can tell whether the word “OUTER” in the Informix SQL above is signifying a left, right, or full outer join? ---
**Author:** ![Parallax](https://avatars.discourse-cdn.com/v4/letter/p/bbce88/32.png) [@Parallax](https://boards.straightdope.com/u/Parallax)\ **Post date:** [August 22, 2004, 2:00am UTC](https://boards.straightdope.com/t/translating-an-outer-join-ridden-sql-statement-from-informix-to-oracle/260866/2 "2004-08-22T02:00:56Z")
It looks like all outer joins in Informix are left outer joins. Take a look at: [http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/infmxsql.mspx](http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/infmxsql.mspx) This document talks about converting Informix to Microsoft SQL Server but the information about Informix should be useful to Oracle as well. Search for “Join Clauses” to find the section that talks about joins. The left outer join section says: This type of join specifies that all of the left table outer rows be returned, even if no column matches are found. This operates just like an Informix outer join. The sections about right and full outer joins says there is no equivalent in Informix. I assume this means previous versions of Informix that didn’t support ANSI syntax. ---
**Author:** ![bordelond](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bordelond/32/150_2.png) [@bordelond](https://boards.straightdope.com/u/bordelond)\ **Post date:** [August 22, 2004, 3:27am UTC](https://boards.straightdope.com/t/translating-an-outer-join-ridden-sql-statement-from-informix-to-oracle/260866/3 "2004-08-22T03:27:38Z")
> [@Parallax](#): > > The sections about right and full outer joins says there is no equivalent in Informix. I assume this means previous versions of Informix that didn’t support ANSI syntax. Thanks, **Parallax**. Hmmm …when I thought about it later, it seemed that it might have been useful to actually break down the Informix WHERE clause, and see how the tables.columns are ordered in each X.x = Y.x statement (if that makes sense). See, I’m used to Oracle versions up to 8i, and it’s 9i that seems to allow the ANSI join syntax (working on a 9i db now). I can define the joins in Oracle 9i the way I would have in previous versions, but I am having trouble “unraveling” the Informaix code. Therefore, I was hoping it would be easier to just go ahead and define the joins in the FROM clause … but I’m having trouble figuring out just what the Informix FROM clause above is telling me. If I really can make 'em all left outer joins, then life becomes much easier. ---
**Author:** ![SCSimmons](https://avatars.discourse-cdn.com/v4/letter/s/e495f1/32.png) [@SCSimmons](https://boards.straightdope.com/u/SCSimmons)\ **Post date:** [August 22, 2004, 6:03pm UTC](https://boards.straightdope.com/t/translating-an-outer-join-ridden-sql-statement-from-informix-to-oracle/260866/4 "2004-08-22T18:03:06Z")
Parallax is correct-Informix SQL outer joins are left outer joins. So this should be fairly simple. Well, that part should, anyway … ---
**Author:** ![paperbackwriter](https://avatars.discourse-cdn.com/v4/letter/p/53a042/32.png) [@paperbackwriter](https://boards.straightdope.com/u/paperbackwriter)\ **Post date:** [August 23, 2004, 7:22pm UTC](https://boards.straightdope.com/t/translating-an-outer-join-ridden-sql-statement-from-informix-to-oracle/260866/5 "2004-08-23T19:22:52Z")
Without knowing the data structure, I’m taking a shot in the dark, but this might get you started. Select SVCORD\_SYSTEM, NSPS\_SYSTEM, PROVIDER PROV1, SVCORD\_CIRCUIT, CIRCUIT, PROVIDER, LOCATION, ADDRESS, OFFNET\_DESC, CUSTOMER, Contact, SERVICE\_ADDRESS,MACD\_SVCORD, MACD\_WORKORDER, CUSTOMERACCOUNT, BILLINGACCOUNT FROM SVCORD\_LOC\_SVC, NC\_REF\_DEC Where SVCORD\_SYSTEM (+)= NSPS\_SYSTEM and PROVIDER PROV1(+)=PROVIDER And SVCORD\_CIRCUIT (+)=CIRCUIT and LOCATION(+)=ADDRESS and CUSTOMER(+)=Contact and MACD\_SVCORD(+)=MACD\_WORKORDER and CUSTOMERACCOUNT(+)=BILLINGACCOUNT ---
**Author:** ![bordelond](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bordelond/32/150_2.png) [@bordelond](https://boards.straightdope.com/u/bordelond)\ **Post date:** [August 23, 2004, 7:52pm UTC](https://boards.straightdope.com/t/translating-an-outer-join-ridden-sql-statement-from-informix-to-oracle/260866/6 "2004-08-23T19:52:14Z")
> [@paperbackwriter](#): > > Without knowing the data structure, I’m taking a shot in the dark, but this might get you started. > Select SVCORD\_SYSTEM, NSPS\_SYSTEM, PROVIDER PROV1, SVCORD\_CIRCUIT, CIRCUIT, PROVIDER, > LOCATION, ADDRESS, OFFNET\_DESC, CUSTOMER, Contact, SERVICE\_ADDRESS,MACD\_SVCORD, > MACD\_WORKORDER, CUSTOMERACCOUNT, BILLINGACCOUNT > FROM SVCORD\_LOC\_SVC, NC\_REF\_DEC > Where SVCORD\_SYSTEM (+)= NSPS\_SYSTEM > and PROVIDER PROV1(+)=PROVIDER > And SVCORD\_CIRCUIT (+)=CIRCUIT > and LOCATION(+)=ADDRESS > and CUSTOMER(+)=Contact > and MACD\_SVCORD(+)=MACD\_WORKORDER > and CUSTOMERACCOUNT(+)=BILLINGACCOUNT Thanks for the effort, **paperbackwriter**. In this case, though, the Informix FROM statement plays the same role as an Oracle FROM statment. In the OP, I left the SELECT statement out because there was no question about it – it’s pretty straightforward. … **SCSimmons, parallax** – if I’m following you two correctly, the following statements should be equivalent: **Informix SQL:** FROM table1 OUTER table 2 WHERE table1.column1 = table2.column1 **Oracle (8i or earlier) SQL:** FROM table1, table 2 WHERE table1.column1 (+) = table2.column1 _or_ FROM table1, table 2 WHERE table2.column1 = table1.column1 (+) **Oracle 9i SQL:** FROM table1 OUTER LEFT JOIN table 2 WHERE table1.column1 = table2.column1 _or_ FROM table2 OUTER RIGHT JOIN table 1 WHERE table2.column1 = table1.column1 _or earlier Oracle version SQL acceptable_ ---
**Author:** ![SCSimmons](https://avatars.discourse-cdn.com/v4/letter/s/e495f1/32.png) [@SCSimmons](https://boards.straightdope.com/u/SCSimmons)\ **Post date:** [August 23, 2004, 11:42pm UTC](https://boards.straightdope.com/t/translating-an-outer-join-ridden-sql-statement-from-informix-to-oracle/260866/7 "2004-08-23T23:42:41Z")
Sadly, I know Informix SQL much better than Oracle, which I don’t usually have to work with. But assuming Oracle 9i uses basically standard outer join syntax, I’d say you’ve got it. (And I gleefully walk away clutching the Oracle 8i outer join syntax, which I’d never taken the time to research. 🙂 )