Outer Joins

This section shows how to create and use outer joins in a SELECT statement. Creating a Join discusses inner joins. Whereas an inner join treats two or more joined tables equally, an outer join treats two or more joined tables asymmetrically. An outer join makes one of the tables dominant (also called the outer table) over the other subordinate tables (also called inner tables).

In an inner join or in a simple join, the result contains only the combinations of rows that satisfy the join conditions. Rows that do not satisfy the join conditions are discarded.

In an outer join, the result contains the combinations of rows that satisfy the join conditions and the rows from the dominant table that would otherwise be discarded because no matching row was found in the subordinate table. The rows from the dominant table that do not have matching rows in the subordinate table contain NULL values in the columns selected from the subordinate table.

An outer join allows you to apply join filters to the inner table before the join condition is applied.

Earlier versions of the database server supported only the Informix extension to the ANSI-SQL standard syntax for outer joins. This syntax is still supported. However, the ANSI-SQL standard syntax provides for more flexibility with creating queries. It is recommended that you use the ANSI-SQL standard syntax to create new queries. Whichever form of syntax you use, you must use it for all outer joins in a single query block.

Before you rely on outer joins, determine whether one or more inner joins can work. You can often use an inner join when you do not need supplemental information from other tables.

For information on the syntax of outer joins, see the IBM Informix: Guide to SQL Syntax.

Informix Extension to Outer Join Syntax

The Informix extension to outer-join syntax begins an outer join with the OUTER keyword. When you use the Informix syntax, you must include the join condition in the WHERE clause. When you use the Informix syntax for an outer join, the database server supports the following three basic types of outer joins:

• A simple outer join on two tables
• An outer join for a simple join to a third table
• An outer join of two tables to a third table

An outer join must have a Projection clause, a FROM clause, and a WHERE clause. The join conditions are expressed in a WHERE clause. To transform a simple join into an outer join, insert the keyword OUTER directly before the name of the subordinate tables in the FROM clause. As shown later in this section, you can include the OUTER keyword more than once in your query.

No Informix extension to outer-join syntax is equivalent to the ANSI right outer join.

ANSI Join Syntax

The following ANSI joins are supported:

• Left outer join
• Right outer join

The ANSI outer-join syntax begins an outer join with the LEFT JOIN, LEFT OUTER JOIN, RIGHT JOIN, or RIGHT OUTER JOIN keywords. The OUTER keyword is optional. Queries can specify a join condition and optional join filters in the ON clause. The WHERE clause specifies a post-join filter. In addition, you can explicitly specify the type of join using the LEFT or right clause. ANSI join syntax also allows the dominant or subordinate part of an outer join to be the result set of another join, when you begin the join with a left parenthesis.

If you use ANSI syntax for an outer join, you must use the ANSI syntax for all outer joins in a single query block.

Left Outer Join

In the syntax of a left outer join, the dominant table of the outer join appears to the left of the keyword that begins the outer join. A left outer join returns all of the rows for which the join condition is true and, in addition, returns all other rows from the dominant table and displays the corresponding values from the subservient table as NULL.

The following query uses ANSI syntax LEFT OUTER JOIN to achieve the same results as Figure 291, which uses the Informix outer-join syntax:

SELECT c.customer_num, c.lname, c.company, c.phone, 
   u.call_dtime, u.call_descr
FROM customer c LEFT OUTER JOIN cust_calls u 
ON c.customer_num = u.customer_num

In this example, you can use the ON clause to specify the join condition.You can add an additional filter in the WHERE clause to limit your result set; such a filter is a post-join filter.

The following query returns only rows in which customers have not made any calls to customer service. In this query, the database server applies the filter in the WHERE clause after it performs the outer join on the customer_num column of the customer and cust_calls tables.

SELECT c.customer_num, c.lname, c.company, c.phone, 
   u.call_dtime, u.call_descr 
FROM customer c LEFT OUTER JOIN cust_calls u 
ON c.customer_num = u.customer_num 
WHERE u.customer_num IS NULL

In addition to the previous examples, the following examples show various types of query constructions that are available with ANSI join syntax:

SELECT *
FROM (t1 LEFT OUTER JOIN (t2 LEFT OUTER JOIN t3 ON t2.c1=t3.c1)
   ON t1.c1=t3.c1) JOIN (t4 LEFT OUTER JOIN t5 ON t4.c1=t5.c1)
   ON t1.c1=t4.c1;

SELECT *
FROM (t1 LEFT OUTER JOIN (t2 LEFT OUTER JOIN t3 ON t2.c1=t3.c1) 
   ON t1.c1=t3.c1), 
   (t4 LEFT OUTER JOIN t5 ON t4.c1=t5.c1)
   WHERE t1.c1 = t4.c1;

SELECT *
FROM (t1 LEFT OUTER JOIN (t2 LEFT OUTER JOIN t3 ON t2.c1=t3.c1)
   ON t1.c1=t3.c1) LEFT OUTER JOIN (t4 JOIN t5 ON t4.c1=t5.c1)
   ON t1.c1=t4.c1;

SELECT *
FROM t1 LEFT OUTER JOIN (t2 LEFT OUTER JOIN t3 ON t2.c1=t3.c1)
   ON t1.c1=t2.c1;

SELECT *
FROM t1 LEFT OUTER JOIN (t2 LEFT OUTER JOIN t3 ON t2.c1=t3.c1)
   ON t1.c1=t3.c1;

SELECT *
FROM (t1 LEFT OUTER JOIN t2 ON t1.c1=t2.c1)
   LEFT OUTER JOIN t3 ON t2.c1=t3.c1;

SELECT *
FROM (t1 LEFT OUTER JOIN t2 ON t1.c1=t2.c1)
   LEFT OUTER JOIN t3 ON t1.c1=t3.c1;

SELECT *
FROM t9, (t1 LEFT JOIN t2 ON t1.c1=t2.c1),
   (t3 LEFT JOIN t4 ON t3.c1=10), t10, t11,
   (t12 LEFT JOIN t14 ON t12.c1=100);

Right Outer Join (IDS)

In the syntax of a right outer join, the dominant table of the outer join appears to the right of the keyword that begins the outer join. A right outer join returns all of the rows for which the join condition is true and, in addition, returns all other rows from the dominant table and displays the corresponding values from the subservient table as NULL.

Figure 287 is an example of a right outer join on the customer and orders tables.

SELECT c.customer_num, c.fname, c.lname, o.order_num,
o.order_date, o.customer_num
FROM customer c RIGHT OUTER JOIN orders o
ON (c.customer_num = o.customer_num);

Figure 287 returns all rows from the dominant table orders and, as necessary, displays the corresponding values from the subservient table customer as NULL.

customer_num   fname    lname order_num   order_date  customer_num
        104  Anthony  Wiggins      1001   05/30/1998           104
        101  Ludwig     Pauli      1002   05/30/1998           101
        104 Anthony   Wiggins      1003   05/30/1998           104
     <NULL>  <NULL>    <NULL>      1004   06/05/1998           106

Simple Join

Figure 289 is an example of a simple join on the customer and cust_calls tables.

SELECT c.customer_num, c.lname, c.company, 
     c.phone, u.call_dtime, u.call_descr
   FROM customer c, cust_calls u
   WHERE c.customer_num = u.customer_num

Figure 289 returns only those rows in which the customer has made a call to customer service, as Figure 290 shows.

customer_num  106
lname         Watson
company       Watson & Son
phone         415-389-8789
call_dtime    1998-06-12 08:20
call_descr    Order was received, but two of the cans of 
              ANZ tennis balls within the case were empty

·
·
·

customer_num  116
lname         Parmelee
company       Olympic City
phone         415-534-8822
call_dtime    1997-12-21 11:24
call_descr    Second complaint from this customer! Received 
              two cases right-handed outfielder gloves (1 HRO) 
              instead of one case lefties.

Simple Outer Join on Two Tables

Figure 291 uses the same projection clause, tables, and comparison condition as the preceding example, but this time it creates a simple outer join in Informix extension syntax.

SELECT c.customer_num, c.lname, c.company, 
     c.phone, u.call_dtime, u.call_descr
   FROM customer c, OUTER cust_calls u
   WHERE c.customer_num = u.customer_num

The addition of the keyword OUTER before the cust_calls table makes it the subservient table. An outer join causes the query to return information on all customers, whether or not they have made calls to customer service. All rows from the dominant customer table are retrieved, and NULL values are assigned to columns of the subservient cust_calls table, as Figure 292 shows.

customer_num  101
lname         Pauli
company       All Sports Supplies
phone         408-789-8075
call_dtime    
call_descr    

customer_num  102
lname         Sadler
company       Sports Spot
phone         415-822-1289
call_dtime    
call_descr    

·
·
·

customer_num  107
lname         Ream
company       Athletic Supplies
phone         415-356-9876
call_dtime    
call_descr    

customer_num  108
lname         Quinn
company       Quinn's Sports
phone         415-544-8729
call_dtime    
call_descr   

Outer Join for a Simple Join to a Third Table

Using ANSI syntax, Figure 293 shows an outer join that is the result of a simple join to a third table. This second type of outer join is known as a nested simple join.

SELECT c.customer_num, c.lname, o.order_num, 
     i.stock_num, i.manu_code, i.quantity
   FROM customer c, LEFT OUTER JOIN (orders o, items i)
   WHERE c.customer_num = o.customer_num
      AND o.order_num = i.order_num
      AND manu_code IN ('KAR', 'SHM')
   ORDER BY lname

Figure 293 first performs a simple join on the orders and items tables, retrieving information on all orders for items with a manu_code of KAR or SHM. It then performs an outer join to combine this information with data from the dominant customer table. An optional ORDER BY clause reorganizes the data into the form that Figure 294 shows.

customer_num lname             order_num stock_num manu_code quantity

         114 Albertson
         118 Baxter
         113 Beatty
         
·
·
·

         105 Vector
         121 Wallack                1018       302 KAR              3
         106 Watson

Outer Join of Two Tables to a Third Table

Using Informix extension syntax, Figure 295 shows an outer join that is the result of an outer join of each of two tables to a third table. In this third type of outer join, join relationships are possible only between the dominant table and the subservient tables.

SELECT c.customer_num, c.lname, o.order_num, 
     order_date, call_dtime
   FROM customer c, OUTER orders o, OUTER cust_calls x
   WHERE c.customer_num = o.customer_num
      AND c.customer_num = x.customer_num
   ORDER BY lname
   INTO TEMP service

Figure 295 individually joins the subservient tables orders and cust_calls to the dominant customer table; it does not join the two subservient tables. An INTO TEMP clause selects the results into a temporary table for further manipulation or queries, as Figure 296 shows.

customer_num lname             order_num order_date call_dtime

         114 Albertson
         118 Baxter
         113 Beatty
         103 Currie
         115 Grant                  1010 06/17/1998
         
·
·
·

         117 Sipes                  1012 06/18/1998
         105 Vector
         121 Wallack                1018 07/10/1998 1998-07-10 14:05
         106 Watson                 1004 05/22/1998 1998-06-12 08:20
         106 Watson                 1014 06/25/1998 1998-06-12 08:20

If Figure 295 had tried to create a join condition between the two subservient tables o and x, as Figure 297 shows, an error message would indicate the creation of a two-sided outer join.

WHERE o.customer_num = x.customer_num

Joins That Combine Outer Joins

To achieve multiple levels of nesting, you can create a join that employs any combination of the three types of outer joins. Using ANSI syntax, Figure 298 creates a join that is the result of a combination of a simple outer join on two tables and a second outer join.

SELECT c.customer_num, c.lname, o.order_num, 
     stock_num, manu_code, quantity
   FROM customer c, OUTER (orders o, OUTER items i)
   WHERE c.customer_num = o.customer_num
      AND o.order_num = i.order_num
      AND manu_code IN ('KAR', 'SHM')
   ORDER BY lname

Figure 298 first performs an outer join on the orders and items tables, retrieving information on all orders for items with a manu_code of KAR or SHM. It then performs a second outer join that combines this information with data from the dominant customer table.

customer_num lname             order_num stock_num manu_code quantity

         114 Albertson
         118 Baxter
         113 Beatty
         103 Currie
         115 Grant                  1010
         
·
·
·

         117 Sipes                  1012
         117 Sipes                  1007
         105 Vector
         121 Wallack                1018       302 KAR              3
         106 Watson                 1014
         106 Watson                 1004

You can specify the join conditions in two ways when you apply an outer join to the result of an outer join to a third table. The two subservient tables are joined, but you can join the dominant table to either subservient table without affecting the results if the dominant table and the subservient table share a common column.