Informix-Extension Outer Joins

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

This syntax fragment is part of the FROM Clause.


Informix OUTER Clause

|--OUTER-------------------------------------------------------->

                       (1)                                                                  
>--+-+-| ANSI Tables |------+-----------------------------------------------------------+--|
   | |                  (1) |                                                           |   
   | '-| Other Tables |-----'                                                           |   
   |    .-,------------------------------------------------------------------------.    |   
   |    |   .-,------------------------.     .-,-------------------------------.   |    |   
   |    V   V                   (1)    |     V  (2)                            |   |    |   
   '-(----+---+-| ANSI Tables |------+-+--,----------| Informix OUTER Clause |-+-+-+--)-'   
          |   |                  (1) |                                           |          
          |   '-| Other Tables |-----'                                           |          
          | .-,-------------------------------.     .-,------------------------. |          
          | V  (2)                            |     V                   (1)    | |          
          '---------| Informix OUTER Clause |-+--,----+-| ANSI Tables |------+-+-'          
                                                      |                  (1) |              
                                                      '-| Other Tables |-----'

Notes:

See FROM Clause
Informix extension

The following 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 fails.

The following example, however, successfully returns a result

that joins the dominant customer table with the subordinate orders table,
and joins the dominant customer table with the subordinate cust_calls table:

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.

Restrictions on Informix-extension outer joins

If you use this Informix-extension syntax for an outer join, all of the following restrictions apply to the same SELECT statement:

You must use Informix-extension syntax for all outer joins in a single query block.
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.
You cannot define a lateral table reference or include the LATERAL keyword.
Within the Informix-extension outer join, the Table Reference syntax segment cannot include a lateral table reference that is declared in the same SELECT statement.