SQL adaptation guide For IBM DB2 UDB 8.x, 9x / Data manipulation |
The original OUTER join syntax of Informix® is different from the IBM® DB2® outer join syntax:
SELECT ... FROM cust, OUTER(order) WHERE cust.key = order.custno SELECT ... FROM cust,OUTER(order,OUTER(item)) WHERE cust.key = order.custno AND order.key = item.ordno AND order.accepted = 1
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.accepted = 1
See the IBM DB2 SQL 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 IBM DB2 interface can convert most Informix OUTER specifications to IBM DB2 outer joins.
Prerequisites:
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"