Outer joins

The original syntax of OUTER joins of Informix^® is different from the SQLite outer join syntax:

In Informix SQL, outer tables are 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

SQLite 3 supports the ANSI outer join syntax:

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.

Solution

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:

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:

Additional conditions on outer table columns cannot be detected and therefore are not supported:
Example: "... FROM tab1, OUTER(tab2) WHERE tab1.col1 = tab2.col2 AND tab2.colx > 10".
Statements composed by 2 or more SELECT instructions using OUTERs are not supported.
Example: "SELECT ... UNION SELECT" or "SELECT ... WHERE col IN (SELECT...)"

Remarks:

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.