Outer joins

In Informix^® SQL, outer joins can be defined in the FROM clause with the OUTER keyword:

SELECT ... FROM a, OUTER (b) WHERE a.key = b.akey

SELECT ... FROM a, OUTER(b,OUTER(c)) WHERE a.key = b.akey 
  AND b.key1 = c.bkey1 AND b.key2 = c.bkey2

ORACLE expects the (+) operator in the join condition. You must set a (+) after columns of the tables which must have NULL values when no record matches the condition:

SELECT ... FROM a, b WHERE a.key = b.key (+)

SELECT ... FROM a, b, c WHERE a.key = b.akey (+)>
   AND b.key1 = c.bkey1 (+)
   AND b.key2 = c.bkey2 (+)

When using additional conditions on outer tables, the (+) operator also has to be used. For example:

SELECT ... FROM a, OUTER(b) WHERE a.key = b.akey AND b.colx > 10

Must be converted to:

SELECT ... FROM a, b WHERE a.key = b.akey (+)
   AND b.colx (+) > 10

The ORACLE outer joins restriction:

In a query that performs outer joins of more than two pairs of tables, a single table can only be the NULL generated table for one other table. The following case is not allowed: WHERE a.col = b.col (+) AND b.col (+) = c.col

Solution

For better SQL portability, you should use the ANSI outer join syntax instead of the old Informix OUTER syntax.

The Oracle interface can convert most Informix OUTER specifications to Oracle outer joins.

Prerequisites:

In the FROM clause, the main table must be the first item and the outer tables must be listed from left to right in the order of outer levels.
Example which does not work: "FROM OUTER(tab2), tab1 "
The outer join in the WHERE clause must use the table name as prefix.
Example: "WHERE tab1.col1 = tab2.col2 "

Restrictions:

Statements composed by 2 or more SELECT instructions are not supported.
Example: "SELECT ... UNION SELECT" or "SELECT ... WHERE col IN (SELECT...)"

Note:

Table aliases are detected in OUTER expressions.
OUTER example with table alias: "OUTER( tab1 alias1)".
In the outer join, <outer table>.<col> can be placed on both right or left sides of the equal sign.
OUTER join example with table on the left: "WHERE outertab.col1 = maintab.col2 "
Table names detection is not case-sensitive.
Example: "SELECT ... FROM tab1, TAB2 WHERE tab1.col1 = tab2.col2"
Temporary tables are supported in OUTER specifications.