Outer joins escape sequence

ODBC supports the SQL92 left, right, and full outer join syntax. The escape sequence for outer joins is:

{oj outer-join}

where outer-join is:

table-reference {LEFT | RIGHT | FULL} OUTER JOIN {table-reference | outer-join} ON search-condition

table-reference specifies a table name, outer-join specifies an secondary outer join, and search-condition specifies the join condition between the table references.

An outer join request must appear after the FROM keyword and before the WHERE clause (if one exists).

For example, the following SQL statements create the same result set that lists all customers and shows which have open orders. The first statement uses the escape-sequence syntax. The second statement uses the native syntax for Oracle and is not interoperable.

SELECT Customers.CustID, Customers.Name, Orders.OrderID, Orders.Status
FROM {oj Customers LEFT OUTER JOIN Orders ON Customers.CustID=Orders.CustID}
WHERE Orders.Status='OPEN'

SELECT Customers.CustID, Customers.Name, Orders.OrderID, Orders.Status
FROM Customers, Orders
WHERE (Orders.Status='OPEN') AND (Customers.CustID= Orders.CustID(+))

To determine the types of outer joins that a data source and driver support, an application calls SQLGetInfo with the SQL_OJ_CAPABILITIES flag. The types of outer joins that might be supported are left, right, full, or nested outer joins. Supported types might also include:

• Outer joins in which the column names in the ON clause do not have the same order as their respective table names in the OUTER JOIN clause
• Inner joins in conjunction with outer joins, and
• Outer joins using any ODBC comparison operator.

If the SQL_OJ_CAPABILITIES information type returns 0, no outer join clause is supported.