SQL adaptation guide For Oracle MySQL 5.x / 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 cust LEFT OUTER JOIN order ON cust.key = order.custno SELECT ... FROM cust LEFT OUTER JOIN order LEFT OUTER JOIN item ON order.key = item.ordno ON cust.key = order.custno WHERE order.cdate > current date
See the MySQL reference for a complete description of the syntax.
For better SQL portability, you should use the ANSI outer join syntax instead of the old Informix OUTER syntax.
The MySQL interface can convert most Informix OUTER specifications to ANSI outer joins.
Prerequisites:
Example which does not work: "FROM OUTER(tab2), tab1".
Example: "WHERE tab1.col1 = tab2.col2".
Restrictions:
Example: "... FROM tab1, OUTER(tab2) WHERE tab1.col1 = tab2.col2 AND tab2.colx > 10".
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".
are supported in OUTER specifications.