SQL adaptation guide For SQLite 3.5.x, 3.6.x / Data manipulation |
The original syntax of OUTER joins of Informix® is different from the SQLite outer join syntax:
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.accepted = 1
See the SQLite 3 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 SQLite 3 interface can convert most Informix OUTER specifications to SQLite 3 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...)"
Remarks:
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".