Informix Outer Joins

This document contains two ancient appendixes describing Informix outer joins, along with some commentary.

Version 1.10
Version 4.0
Commentary

The Spring 1987 issue of Informix's Tech Notes had an article on Outer Joins. The content of that article is the basis of the version 4.0 appendix with minimal changes for the altered context, such as omitting a reference to the next article in the Tech Notes which was on query optimization.


Appendix G, Informix-4GL Reference Manual

Version 1.10

COMPLEX OUTER JOINS

Jonathan Leffler comments:

This is a transcription and translation into HTML of an ancient document, namely Appendix G (Complex Outer Joins) from the Informix-4GL Reference Manual, Vol 2, Version 1.10 (part number 200-507-0002-0, dated July 1988). It is referring to Informix's OUTER construct, not the ISO 9075:1992 SQL standard construct with the same name but a radically different syntax. The ISO (ANSI) outer join notation is available IDS 7.31 and in IDS.2000 9.21. Also, the document uses the term RDSQL to refer to the database server. The original version of this document dates back to the days before SE and Turbo were separate products, when you bought Informix-4GL or Informix-SQL and the database process, sqlexec (which became the core of SE) was simply an important part of the base product.

End commentary

Complex Outer Joins

When more than two table participate in an outer join, there are many ways in which to relate them. For three tables, there are five logically distinct joins, of which four involve outer joins. These distinct outer joins are listed later in this appendix, using the notation to be used in SELECT statements and a graphic notation that can be generalized for any number of tables. The detailed rules for the graphic representation are located at the end of this section. Briefly, the subservient tables are placed on a lower level than dominant tables.

The two-table outer join described in Chapter 2 [of the I4GL Reference Manual, Volume 1] is graphically represented as follows:


SELECT a, b
    FROM tab1, OUTER tab2
    WHERE a = b

Diagram
tab1 -------@           Level 1
            |
          tab2          Level 2

In the examples that follow, the tilde (~) symbol represents any Boolean relation between the columns of two tables.

Example 1


    FROM x, y, z
    WHERE ...

Diagram
x ---- y ---- z         Level 1

This is the standard inner join and all three tables are treated on the same level. There are no restrictions on the WHERE clause. It need not exist. Before the WHERE clause is applied, a full Cartesian product is made among all three tables. (Note: A Cartesian product of two tables appends every row of the second table to every row of the first. A Cartesian product of three tables is the Cartesian product of the third with the Cartesian product of the first two, and so on.)

Example 2


    FROM x, y, OUTER z
    WHERE (x or y) ~ z

Diagram
x ---- y -----@         Level 1
              |
              z         Level 2

Example 2 illustrates the simplest extension of the outer join from two tables to three tables. x and y are joined at the same level and are shown connected together. z is the subservient table and is placed down one level. The WHERE clause must relate a Level 1 table with a Level 2 table. Other relationships are also permitted. Before the WHERE clause is applied, RDSQL takes the cartesian product of x and y and then takes the outer join of the result with z. x

Example 3


    FROM x, OUTER y, OUTER z
    WHERE x ~ y AND x ~ z

Diagram
x -----@------@         Level 1
       |      |
       y      z         Level 2

Although both y and z are at the same level, they are not connected. Here, a relationship is required between x and y and also between x and z. No relationship is permitted between y and z since they are not connected at the second level. The outer join is performed between x and y and then the outer join between the resulting table and z.

Example 4


    FROM x, OUTER (y, z)
    WHERE x ~ (y or z)

Diagram
x -----@                Level 1
       |
       y ---- z         Level 2

In Example 4, a relationship is required between x and either y or z or both. In addition, there may be a relationship between y and z since they are connected at the second level. To perform this join, RDSQL starts at the top and works down. For each row of x, RDSQL attempts to find a row of y that satisfies the WHERE clause. If none is found, RDSQL substitutes NULL values for the columns of y and z. If a row of y satisfies the WHERE clause, RDSQL seeks a row of z that satisfies the WHERE clause. It substitutes NULL values for both y and z if no row in z is found.

Example 5


    FROM x, OUTER (y, OUTER z)
    WHERE x ~ y AND y ~ z

Diagram
x -----@                Level 1
       |
       y -----@         Level 2
              |
              z         Level 3

Example 5 shows a three-level outer join. There must always be a relationship between adjacent levels. In this case, x must be related to y, and y must be related to z. RDSQL performs this join by picking a row from x and looking for a pair from y and z that satisfies (y, outer z).

Output from Examples

If each of the tables consists of a single column with the values in the followin table, then the previously described joins will result in the subsequent tables.


    x.a     y.b     z.c
    1       2       3
    2       3       4
    3       4       5
    5


Example 1


    FROM x, y, z
    WHERE x.a = z.c
    a       b       c
    3       2       3
    3       3       3
    3       4       3
    5       2       5
    5       3       5
    5       4       5

[JL: this example, and several of the following ones, has a Cartesian product of table y with the join of tables x and z.]


Example 2


    FROM x, y, OUTER z
    WHERE x.a = z.c
    a       b       c
    1       2       -
    1       3       -
    1       4       -
    2       2       -
    2       3       -
    2       4       -
    3       2       3
    3       3       3
    3       4       3
    5       2       5
    5       3       5
    5       4       5

Example 3


    FROM x, OUTER y, OUTER z
    WHERE x.a = y.b AND x.a = z.c
    a       b       c
    1       -       -
    2       2       -
    3       3       3
    5       -       5

Example 4


    FROM x, OUTER (y, z)
    WHERE x.a = z.c
    a       b       c
    1       -       -
    2       -       -
    3       2       3
    3       3       3
    3       4       3
    5       2       5
    5       3       5
    5       4       5

Example 5


    FROM x, OUTER (y, OUTER z)
    WHERE x.a = y.b AND y.b = z.c
    a       b       c
    1       -       -
    2       2       -
    3       3       3
    5       -       -

The following steps are useful when considering how RDSQL performs an outer join:

  1. Draw the graph corresponding to the FROM clause. Replace each keyword OUTER with the symbol @ and put the table names to which the keyword applies in the next lower level.
  2. Ensure that a condition exists in the WHERE clause relating a table on each level through each @ to a table in the next level below.
  3. Form a Cartesian product of all tables connected on the same level, applying whichever conditions of the WHERE clause apply only to that level.
  4. Starting with the first level, take a row at a time from the resulting table and attempt to satisfy the WHERE clause with the resulting table of the next lower level, replacing the columns of the lower-level table with NULL values if the WHERE clause cannot be satisfied.
As an example, the following FROM clause results in the accompanying graph:
    FROM x, OUTER (y, OUTER (z, a, b)), outer c, d
x -----@-------------@----- d   Level 1
       |             |
       y ---- @      c          Level 2
              |
              z ---- a ---- b   Level 3

A Cartesian product is made between x and d (call it txd) and between z, a and b (call it tzab), since these sets of tables are connected on the same level. y and c are both on Level 2, but they are not connected on that level. For each row of txd, RDSQL attempts to find a row of y that satisfies the WHERE clause. If it succeeds, it searches for a row of tzab that satisfies the WHERE clause. In both cases, NULL values are substituted if no satisfactory row is found. RDSQL then searches for a row of c that satisfies the WHERE clause, substituting NULL values if unsuccessful.


This is the end of Appendix G in the 1.10 manual.

Appendix G, Informix-4GL Reference Manual

Version 4.0

OUTER JOINS

Jonathan Leffler comments:

This is a transcription and translation into HTML of an ancient document, namely Appendix G (Outer Joins) from the Informix-4GL Reference Manual, Vol 2, Version 4.0 (part number 000-7405, dated March 1990). It is referring to Informix's OUTER construct, not the ISO 9075:1992 SQL standard construct with the same name but a radically different syntax. The ISO (ANSI) outer join notation is understood in IDS 7.31 and in IDS.2000 9.21. Note that the document uses the term '4GL' to refer to the database server.

End commentary

Outer Joins

This appendix discusses the difference between a simple join and an outer join, and describes in detail how outer joins work. The following SELECT statements illustrate the basic difference between the two types of join.

Query 1 - Using a Simple Join


SELECT customer.customer_num, lname, order_num
    FROM customer, orders
    WHERE customer.customer_num = orders.order_num

Query 2 - Using an Outer Join

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

Both query the same tables (customer and orders) of the same database (stores) through a join on the same column (customer_num). At first glance, both fetch the same data. The query results, however, are quote different, as the following illustration shows.

Query Results from Stores7 Database Generated on Solaris 7 with IDS.2000 9.20.UC2.


Query 1 Results

104             Higgins         1001
101             Pauli           1002
104             Higgins         1003
106             Watson          1004
116             Parmelee        1005
112             Lawson          1006
117             Sipes           1007
110             Jaeger          1008
111             Keyes           1009
115             Grant           1010
104             Higgins         1011
117             Sipes           1012
104             Higgins         1013
106             Watson          1014
110             Jaeger          1015
119             Shorter         1016
120             Jewell          1017
121             Wallack         1018
122             O'Brian         1019
123             Hanlon          1020
124             Putnum          1021
126             Neelie          1022
127             Satifer         1023


Query 2 Results

customer_num    lname           order_num
101             Pauli           1002
102             Sadler
103             Currie
104             Higgins         1001
104             Higgins         1003
104             Higgins         1011
104             Higgins         1013
105             Vector
106             Watson          1004
106             Watson          1014
107             Ream
108             Quinn
109             Miller
110             Jaeger          1008
110             Jaeger          1015
111             Keyes           1009
112             Lawson          1006
113             Beatty
114             Albertson
115             Grant           1010
116             Parmelee        1005
117             Sipes           1007
117             Sipes           1012
118             Baxter
119             Shorter         1016
120             Jewell          1017
121             Wallack         1018
122             O'Brian         1019
123             Hanlon          1020
124             Putnum          1021
125             Henry
126             Neelie          1022
127             Satifer         1023
128             Lessor

By using a simple join, Query 1 fetches a list of only those customers who have items on order, while Query 2 fetches a list of all customers by using an outer join. Once you understand how similar queries can produce such dissimilar results, you can begin to use outer joins effectively. The obvious differences between the two joins are as follows:

  • A simple join discards all rows that do not satisfy the join condition.
  • An outer join preserves rows that would otherwise be discarded.

    The following section discusses outer joins in detail.

    How Outer Joins Work

    A join queries two or more tables as though they were one. It is as if 4GL creates and then acts upon a single temporary table to produce the query results. 4GL does not actually create such a table to perform a join, but it is helpful to conceptualize a join in these terms.

    In a simple two-table join, the resulting "table" contains only those combinations of rows from both tables that satisfy the join condition. In an outer join, the resulting "table" contains these rows, plus all the remaining rows from on of the tables, called the dominant (or preserved) table. The second table is called the subservient table.

    Consider two hypothetical tables, employees and depts, which contain the following columns and rows (where dash '--' indicates a NULL value):

    employees
    
    emp_num     dept_num
    2           105
    4           103
    6           103
    5           --
    3           102
    
    depts
    
    depts_num   dept_loc
    
    102         NY
    103         LA
    105         SF
    
    

    Suppose, for example, that you need a list of employee numbers and department locations for all employees, including those employees whose department locations are unknown (represented by NULL values in the employees table). The following query fetches the desired results:


    SELECT emp_num, dept_loc
        FROM employees, OUTER depts
        WHERE employees.dept_num = depts.dept_num
    

    The keyword OUTER designates depts as the subservient table, making employees the dominant table. 4GL processes the query by the following steps:

    1. 4GL applies filters to the subservient table while sequentially applying the join condition to the rows of the dominant table. Rows in the dominant table are retrieved without considering the join, but rows from the subservient table (outer table) are retrieved only if they satisfy the join condition. Any dominant-table rows that do not have a matching row from the subservient table receive a row of NULL values in place of a subservient-table row.

      The result is a "table" with the following rows:

      emp_num     dept_num    dept_num    dept_loc
      2           105         105         SF
      4           103         103         LA
      6           103         103         LA
      5           --          --          --
      3           102         102         NY
      

      Note: A filter is a condition expressed in a WHERE clause that applies to columns in a single table. For example,

      dept_loc  = "SF"        or          emp_num < 105
      

      Because 4GL applies such filters to the subservient table as it performs the join, the resulting "table" may contain NULL values that were not present in the subservient table prior to the join.

      Suppose that the query include a filter on the dept_loc column:


      SELECT emp_num, dept_loc
          FROM employees, OUTER depts
          WHERE employees.dept_num = depts.dept_num
            AND dept_loc != "LA"
      

      At step 2, the results include more rows of NULL values than the results of the original query:

      emp_num     dept_loc
      2           SF
      4           --
      6           --
      5           --
      3           NY
      

      The filter removes rows from the depts table where dept_loc is equal to "LA".

    2. After performing the join, 4Gl applies filters to the dominant table (if they exist).
    3. 4GL applies the SELECT clause to eliminate unneeded columns, and the query returns the results:
      emp_num     dept_loc
      2           SF
      4           LA
      6           LA
      5
      3           NY
      

      The original version listed the emp_num values in numeric order without any sorting. IDS.2000 does not do this.

    In a similar way to the previous example, the following query produces a list of all customers with supplemental information for those customers with items on orders. Where orders.customer_num is not equal to customer.customer_num, 4GL combines a row of NULL values with the corresponding row from the customer table. Because the query does not contain filters, the results preserve every row from the dominant table.

    Query 3


    SELECT customer.customer_num, company, order_num, ship_date
        FROM customer, OUTER orders
        WHERE customer.customer_num = orders.customer_num
    

    Query 3 Results

    customer_num    company                 order_num   ship_date
    101             All Sports Supplies     1002        26/05/1998
    102             Sports Spot
    103             Phil's Sports
    104             Play Ball!              1001        01/06/1998
    104             Play Ball!              1003        23/05/1998
    104             Play Ball!              1011        03/07/1998
    104             Play Ball!              1013        10/07/1998
    105             Los Altos Sports
    106             Watson & Son            1004        30/05/1998
    106             Watson & Son            1014        03/07/1998
    107             Athletic Supplies
    108             Quinn's Sports
    109             Sport Stuff
    110             AA Athletics            1008        06/07/1998
    110             AA Athletics            1015        16/07/1998
    111             Sports Center           1009        21/06/1998
    112             Runners & Others        1006
    113             Sportstown
    114             Sporting Place
    115             Gold Medal Sports       1010        29/06/1998
    116             Olympic City            1005        09/06/1998
    117             Kids Korner             1007        05/06/1998
    117             Kids Korner             1012        29/06/1998
    118             Blue Ribbon Sports
    119             The Triathletes Club    1016        12/07/1998
    120             Century Pro Shop        1017        13/07/1998
    121             City Sports             1018        13/07/1998
    122             The Sporting Life       1019        16/07/1998
    123             Bay Sports              1020        16/07/1998
    124             Putnum's Putters        1021        25/07/1998
    125             Total Fitness Sports
    126             Neelie's Discount Sp    1022        30/07/1998
    127             Big Blue Bike Shop      1023        30/07/1998
    128             Phoenix University
    
    

    The preceeding example queries two tables in the simplest type of outer join. You can, in fact, use outer joins to query any number of tables, producing more types of joins than can be discussed here. The following types are possible when three tables are involved in a query:

  • You can outer-join the result of a simple join to a third table.
    SELECT column-list
        FROM x, OUTER (y, z)
        WHERE x.a = y.a
          AND y.b = z.b
    

    Query 4 performs this kind of join. (See the sections "Examples" later in this chapter.)
  • You can outer-join the result of an outer join to a third table.
    SELECT column-list
        FROM x, OUTER (y, OUTER z)
        WHERE x.a = y.a
          AND y.b = z.b
    

    or
    SELECT column-list
        FROM x, OUTER (y, OUTER z)
        WHERE x.a = z.a     -- different join here!
          AND y.b = z.b
    

    Query 5 and Query 6 perform this kind of join. (See the sections "Examples" later in this chapter.)
  • You can outer-join two tables individually to a third tables, in which case, join relationships are possible only between the subservient tables and the dominant tables. Query 7 performs this kind of join. (See the sections "Examples" later in this chapter.)
    SELECT column-list
        FROM x, OUTER y, OUTER z
        WHERE x.a = y.a
          AND x.b = z.b
    

    When you outer-join several tables to another table, make sure that your WHERE clause does not attempt to specify impossible join conditions. The following query attempts a join between two subservient tables:
    SELECT column-list
        FROM x, OUTER y, OUTER z
        WHERE x.a = y.a
          AND y.b = z.b
    

    An error results: every outer join must have a dominant table.
  • The following examples use the stores database to demonstrate common multi-table outer joins.

    Examples

    Query 4

    This query outer-joins the result of a simple join to a third table. It produces a list of all customers with supplemental information (order number, stock number, manufacturer code, and quantity ordered) for those customers who ordered items manufactured by Anza.
    SELECT customer.customer_num, lname, orders.order_num, stock_num, manu_code, quantity
        FROM customer, OUTER(orders, items)
        WHERE customer.customer_num = orders.customer_num
          AND orders.order_num = items.order_num
          AND manu_code = 'ANZ'
    

    4GL performs the simple join between orders and items first, yielding information on all orders for ANZA-manufactured items. The outer join combines the customer table with the Anza order information. The query results do not include orders for other items.

    Query 4 Results

    customer_num    lname           order_num       stock_num       manu_code       quantity
    101             Pauli
    102             Sadler
    103             Currie
    104             Higgins         1003            9               ANZ             1
    104             Higgins         1003            8               ANZ             1
    104             Higgins         1003            5               ANZ             5
    104             Higgins         1011            5               ANZ             5
    104             Higgins         1013            5               ANZ             1
    104             Higgins         1013            6               ANZ             1
    104             Higgins         1013            9               ANZ             2
    105             Vector
    106             Watson
    107             Ream
    108             Quinn
    109             Miller
    110             Jaeger          1008            8               ANZ             1
    110             Jaeger          1008            9               ANZ             5
    111             Keyes
    112             Lawson          1006            5               ANZ             5
    112             Lawson          1006            6               ANZ             1
    113             Beatty
    114             Albertson
    115             Grant           1010            6               ANZ             1
    116             Parmelee        1005            5               ANZ             10
    116             Parmelee        1005            6               ANZ             1
    117             Sipes           1012            8               ANZ             1
    117             Sipes           1012            9               ANZ             10
    118             Baxter
    119             Shorter
    120             Jewell
    121             Wallack
    122             O'Brian
    123             Hanlon
    124             Putnum          1021            201             ANZ             3
    124             Putnum          1021            205             ANZ             2
    125             Henry
    126             Neelie          1022            6               ANZ             2
    127             Satifer         1023            304             ANZ             1
    128             Lessor
    
    

    Query 5

    This query outer-joins the result of an outer join to a third table. When you use a nested outer join, the query preserves the order numbers that Query 4 (using a nested simple join) eliminates. The query results include all orders, whether or not they contain Anza-manufactured items. For other items, the condition
    WHERE manu_code = 'ANZ'
    

    eliminates stock numbers, manufacturer codes and quantities as before.
    SELECT customer.customer_num, lname, orders.order_num, stock_num, manu_code, quantity
        FROM customer, OUTER (orders, OUTER items)
        WHERE customer.customer_num = orders.customer_num
          AND orders.order_num = items.order_num
          AND manu_code = 'ANZ'
    

    Query 5 Results

    customer_num    lname           order_num       stock_num       manu_code       quantity
    
    101             Pauli           1002
    102             Sadler
    103             Currie
    104             Higgins         1001
    104             Higgins         1003            9               ANZ             1
    104             Higgins         1003            8               ANZ             1
    104             Higgins         1003            5               ANZ             5
    104             Higgins         1011            5               ANZ             5
    104             Higgins         1013            5               ANZ             1
    104             Higgins         1013            6               ANZ             1
    104             Higgins         1013            9               ANZ             2
    105             Vector
    106             Watson          1004
    106             Watson          1014
    107             Ream
    108             Quinn
    109             Miller
    110             Jaeger          1008            8               ANZ             1
    110             Jaeger          1008            9               ANZ             5
    110             Jaeger          1015
    111             Keyes           1009
    112             Lawson          1006            5               ANZ             5
    112             Lawson          1006            6               ANZ             1
    113             Beatty
    114             Albertson
    115             Grant           1010            6               ANZ             1
    116             Parmelee        1005            5               ANZ             10
    116             Parmelee        1005            6               ANZ             1
    117             Sipes           1007
    117             Sipes           1012            8               ANZ             1
    117             Sipes           1012            9               ANZ             10
    118             Baxter
    119             Shorter         1016
    120             Jewell          1017
    121             Wallack         1018
    122             O'Brian         1019
    123             Hanlon          1020
    124             Putnum          1021            201             ANZ             3
    124             Putnum          1021            205             ANZ             2
    125             Henry
    126             Neelie          1022            6               ANZ             2
    127             Satifer         1023            304             ANZ             1
    128             Lessor
    
    
    In addition to customer, orders and so on, the following queries include a hypothetical table named custnotes, containing the following columns and data:
    customer_num    notes
    
    104             sponsors soccer team
    108             customer for 20 years
    115             opening a second store
    118             new customer
    
    

    Query 6

    This query produces a list of all customers with order number and selected notes.
    SELECT customer.customer_num, orders.order_num, notes
        FROM customer, OUTER (orders, OUTER custnotes)
        WHERE customer.customer_num = orders.customer_num
          AND orders.customer_num = custnotes.customer_num
    

    The outer join between custnotes and orders preserves notes only for customers who also have orders.

    Query 6 Results

    customer_num    order_num       notes
    
    101             1002
    102
    103
    104             1001            sponsors soccer team
    104             1003            sponsors soccer team
    104             1011            sponsors soccer team
    104             1013            sponsors soccer team
    105
    106             1004
    106             1014
    107
    108
    109
    110             1008
    110             1015
    111             1009
    112             1006
    113
    114
    115             1010            opening a second store
    116             1005
    117             1007
    117             1012
    118
    119             1016
    120             1017
    121             1018
    122             1019
    123             1020
    124             1021
    125
    126             1022
    127             1023
    128
    
    
    To preserve notes for customers 108 and 118 who do not have orders, you must outer-join the custnotes table directly with the customer table, as shown in the next query.

    Query 7

    This query outer-joins two tables individually to a third table. It outer joins both orders and custnotes to customer (the dominant table).
    SELECT customer.customer_num, orders.order_num, notes
        FROM customer, OUTER orders, OUTER custnotes
        WHERE customer.customer_num = orders.order_num
          AND customer.customer_num = custnotes.customer_num
    

    Cusomer notes now appear, regardless of whether customers have orders.

    Query 7 Results

    customer_num    order_num       notes
    
    101
    102
    103
    104                             sponsors soccer team
    105
    106
    107
    108                             customer for 20 years
    109
    110
    111
    112
    113
    114
    115                             opening a second store
    116
    117
    118                             new customer
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    
    

    All of the preceding queries fetch information from one table with supplemental information from other tables. When you need similar results, Informix recommends that you use an outer join. When you do not need supplemental information, as is normally the case, use a simple join instead.

    Be aware that your choice of an outer join can influence query optimization and processing. You can use the SET EXPLAIN ON statement to examine how the query processor of Informix-4GL performs simple queries, joins and outer joins.


    This is the end of Appendix G in the 4.0 manual.

    Commentary

    This section applies the diagramming notation from the 'Complex Outer Joins' appendix to the examples in the 'Outer Joins' appendix.

    Query 1

    SELECT customer.customer_num, lname, order_num
        FROM customer, orders
        WHERE customer.customer_num = orders.order_num
    
    customer ---- orders
    

    Query 2

    SELECT customer.customer_num, lname, order_num
        FROM customer, OUTER orders
        WHERE customer.customer_num = orders.customer_num
    
    customer -----@
                  |
                orders
    

    Query 3

    SELECT customer.customer_num, company, order_num, ship_date
        FROM customer, OUTER orders
        WHERE customer.customer_num = orders.customer_num
    
    customer -----@
                  |
                orders
    

    Query 4

    SELECT customer.customer_num, lname, orders.order_num, stock_num, manu_code, quantity
        FROM customer, OUTER(orders, items)
        WHERE customer.customer_num = orders.customer_num
          AND orders.order_num = items.order_num
          AND manu_code = 'ANZ'
    
    customer -----@
                  |
                orders ---- items
    

    Query 5

    SELECT customer.customer_num, lname, orders.order_num, stock_num, manu_code, quantity
        FROM customer, OUTER (orders, OUTER items)
        WHERE customer.customer_num = orders.customer_num
          AND orders.order_num = items.order_num
          AND manu_code = 'ANZ'
    
    customer -----@
                  |
                orders -----@
                            |
                          items
    

    Query 6

    SELECT customer.customer_num, orders.order_num, notes
        FROM customer, OUTER (orders, OUTER custnotes)
        WHERE customer.customer_num = orders.customer_num
          AND orders.customer_num = custnotes.customer_num
    
    customer -----@
                  |
                orders -----@
                            |
                        custnotes
    

    Query 7

    SELECT customer.customer_num, orders.order_num, notes
        FROM customer, OUTER orders, OUTER custnotes
        WHERE customer.customer_num = orders.order_num
          AND customer.customer_num = custnotes.customer_num
    
    customer -----@---------@
                  |         |
                orders  custnotes
    

    An Extreme Case

    Once upon a long time ago (are you sitting comfortably?), there was a system running on Informix-Turbo (the predecessor to Informix-OnLine). The views were rather substantial. This is the text of a create view actually encountered on the Turbo system, prettified for presentation.
    CREATE VIEW IBB_V_Project AS
        SELECT  A.Project_Iref,
                A.Section_Iref,
                B.Section_Eref,
                N.Company_Iref,
                N.Company_Name,
                A.Product_Desc,
                A.Project_Type_Iref,
                D.Project_Type,
                A.Person_Iref,
                F.Full_Name,
                A.Respon_Iref,
                G.Post_Location,
                A.Project_Stat_Iref,
                E.Project_Status,
                A.Source_Iref,
                I.Source,
                A.Sic_Iref,
                L.Sic_Eref,
                A.Op_Activity_Iref,
                M.Op_Activity_Desc,
                A.Involve_Iref,
                K.IBB_Involvement,
                A.Nature_Iref,
                C.Nature_Of_Next_Act,
                A.Internat_Mobile,
                A.Whether_Cop_Case,
                A.Closed_Ind,
                A.Next_Action_Date,
                A.Creation_Date,
                A.Last_Edit_Date,
                A.Last_Editor_Iref,
                H.Logname
    
        FROM    IBB_Project A,
                IBB_Section B,
                IBB_R_Proj_Type D,
                IBB_R_Project_Stat E,
                IBB_Personnel H,
                OUTER IBB_R_Next_Act C,
                OUTER IBB_Personnel F,
                OUTER (IBB_Post_Respon X, OUTER IBB_V_Post_Resp2 G),
                OUTER IBB_R_Source I,
                OUTER IBB_R_Involvement K,
                OUTER IBB_Sic L,
                OUTER IBB_Op_Act M,
                OUTER IBB_V_Proj_Co2 N
    
        WHERE   A.Section_Iref      = B.Section_Iref
          AND   A.Project_Type_Iref = D.Project_Type_Iref
          AND   A.Project_Stat_Iref = E.Project_Stat_Iref
          AND   A.Last_Editor_Iref  = H.Person_Iref
          AND   A.Nature_Iref       = C.Nature_Iref
          AND   A.Person_Iref       = F.Person_Iref
          AND   A.Respon_Iref       = X.Respon_Iref
          AND   X.Respon_Iref       = G.Person_Iref
          AND   A.Source_Iref       = I.Source_Iref
          AND   A.Sic_Iref          = L.Sic_Iref
          AND   A.Op_Activity_Iref  = M.Op_Activity_Iref
          AND   A.Project_Iref      = N.Project_Iref
          AND   A.Involve_Iref      = K.Involve_Iref;
    
    Note that IBB_V_Post_Resp2 and IBB_V_Proj_Co2 are both themselves views. In fact, IBB_V_Proj_Co2 was a 3-table view, exact details unknown but of the form:
    CREATE VIEW IBB_V_Proj_Co2 AS
        SELECT  A.Project_Iref,
                A.Some_Other_Col col01,
                B.Xxxx_Iref,
                B.Some_Other_Col col02,
                C.Yyyy_Iref,
                C.Some_Other_Col col03
        FROM    IBB_Project A,
                OUTER (IBB_R_Xxxx B, IBB_R_Yyyy C)
        WHERE   A.Xxxx_Iref = B.Xxxx_IrEf
          AND   B.Yyyy_Iref = C.Yyyy_Iref;
    
    This means that the IBB_V_Project view has an outer self-join on IBB_Project. The IBB_V_Post_Resp2 view involved 3 tables too, somewhat like:
    CREATE VIEW IBB_V_Post_Resp2 AS
        SELECT  A.Person_Iref,
                A.Some_Other_Col col01,
                B.Xxxx_Iref,
                B.Some_Other_Col col02,
                C.Yyyy_Iref,
                C.Some_Other_Col col03
        FROM    IBB_Personnel A,
                IBB_R_Xxxx B,
                IBB_R_Yyyy C
        WHERE   A.Xxxx_Iref = B.Xxxx_Iref
          AND   B.Yyyy_Iref = C.Yyyy_Iref;
    
    The Zzzz_Iref columns were either SERIAL or INTEGER foreign keys referencing a SERIAL key.

    The primary view definition refers to 14 tables, with 4 inner joins and 9 outer joins. When the cross-referenced views are taken into account, there are 18 tables in total, with 7 inner joins and 10 outer joins. The view was constructed semi-automatically by a database schema design tool.


    The diagrams for these views could be summarized as:

    IBB_V_Project

    Of necessity, this diagram uses the table aliases rather than the table names.
    A ---- B ---- D ---- E ---- H -----@------@------@------@------@------@------@------@
                                       |      |      |      |      |      |      |      |
                                G ---- X      C      F      I      K      L      M      N
    

    IBB_V_Proj_Co2


    IBB_Project -----@
                     |
                 IBB_R_Xxxx -----@
                                 |
                             IBB_R_Yyyy
    

    IBB_V_Post_Resp2


    IBB_Personnel ---- IBB_R_Xxxx ---- IBB_R_Yyyy
    

    Transcribed and annotated by Jonathan Leffler.
    Last updated: 2007-01-09