Informix-Extension Outer Joins

The Informix-extension syntax for outer joins begins with an implicit left outer join. That is, you begin an outer join with the OUTER keyword. This is the syntax of the Informix-extension OUTER clause.

Informix-Extension OUTER Clause:
 
|--OUTER-------------------------------------------------------->
 
                         (1)
>--+-| Table Reference |-------------------------------------------------------------------------+--|
   |    .-,---------------------------------------------------------------------------------.    |
   |    |   .-,------------------------.     .-,----------------------------------------.   |    |
   |    V   V                     (1)  |     V                                     (2)  |   |    |
   '-(----+---| Table Reference |------+--,----| Informix-Extension OUTER Clause |------+-+-+--)-'
          | .-,----------------------------------------.     .-,------------------------. |
          | V                                     (2)  |     V                     (1)  | |
          '---| Informix-Extension OUTER Clause |------+--,----| Table Reference |------+-'
 

1. See FROM Clause
2. Informix extension

If you use this syntax for an outer join, you must use Informix-extension syntax for all outer joins in a single query block, and you must include the join condition in the WHERE clause. You cannot begin another outer join with the LEFT JOIN or the LEFT OUTER JOIN keywords.

This example uses the OUTER keyword to create an outer join that lists all customers and their orders, regardless of whether they have placed orders:

SELECT c.customer_num, c.lname, o.order_num FROM customer c, 
   OUTER orders o WHERE c.customer_num = o.customer_num

This example returns all the rows from the customer table with the rows that match in the orders table. If no record for a customer appears in the orders table, the returned order_num column for that customer has a NULL value.

If you have a complex outer join, that is, the query has more than one outer join, you must either embed the additional outer join or joins in parentheses, as the syntax diagram shows, or establish join conditions, or relationships, between the dominant table and each subordinate table in the WHERE clause.

When an expression or a condition in the WHERE clause relates two subordinate tables, you must use parentheses around the joined tables in the FROM clause to enforce dominant-subordinate relationships, as in this example:

SELECT c.company, o.order_date, i.total_price, m.manu_name
   FROM customer c, 
      OUTER (orders o, OUTER (items i, OUTER manufact m))
   WHERE c.customer_num = o.customer_num
      AND o.order_num = i.order_num 
      AND i.manu_code = m.manu_code;

When you omit parentheses around the subordinate tables in the FROM clause, you must establish join conditions between the dominant table and each subordinate table in the WHERE clause. If a join condition is between two subordinate tables, the query will fail, but the following example successfully returns a result:

SELECT c.company, o.order_date, c2.call_descr
   FROM customer c, OUTER orders o, OUTER cust_calls c2
   WHERE c.customer_num = o.customer_num
      AND c.customer_num = c2.customer_num;

The IBM Informix Guide to SQL: Tutorial has examples of complex outer joins.

WHERE Clause

WHERE Clause:
 
|--WHERE-------------------------------------------------------->
 
   .-Logical_Operator---------------------------------------------.
   V                 (1)                                          |
>----+-| Condition |--------------------------------------------+-+--|
     |          (2)                                             |
     +-| Join |-------------------------------------------------+
     |                         (3)                              |
     +-| Function Expression |----------------------------------+
     +-(subquery)-----------------------------------------------+
     |  (4)                               (5)                   |
     '--------+-(-| Collection Subquery |------)--------------+-'
              |                                          (6)  |
              '-| Statement Local Variable Expressions |------'