SQL adaptation guide For Oracle Database 9.2, 10.x, 11.x, 12x / Data manipulation |
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
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 (+)
SELECT ... FROM a, OUTER(b) WHERE a.key = b.akey AND b.colx > 10
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
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:
Example which does not work: "FROM OUTER(tab2), tab1 ".
Example: "WHERE tab1.col1 = tab2.col2 ".
Restrictions:
Example: "SELECT ... UNION SELECT" or "SELECT ... WHERE col IN (SELECT...)"
OUTER example with table alias: "OUTER( tab1 alias1)".
OUTER join example with table on the left: "WHERE outertab.col1 = maintab.col2 ".
Example: "SELECT ... FROM tab1, TAB2 WHERE tab1.col1 = tab2.col2".