Outer joins

The original OUTER join syntax of Informix^® is different from the Sybase ASE outer join syntax:

In Informix SQL, outer tables can be defined in the FROM clause with the OUTER keyword:

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

Sybase ASE Version 7 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

The old way to define outer joins in Sybase ASE looks like the following:

SELECT ... FROM a, b WHERE a.key *= b.key

See the Sybase ASE reference manual 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 Sybase ASE interface can convert simple Informix OUTER specifications to Sybase ASE ANSI outer joins.

Prerequisites:

The outer join in the WHERE part must use the table name as prefix.
Example: "WHERE tab1.col1 = tab2.col2 ".
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 of 2 or more SELECT instructions using OUTERs are not supported.
Example: "SELECT ... UNION SELECT" or "SELECT ... WHERE col IN (SELECT...)"

Note:

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.