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 for a LEFT OUTER JOIN to join the customer and cust_calls tables, with customer the dominant table:

Figure 1: Query 1

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;

The next query similarly uses the ON clause to specify the join condition, and adds an additional filter in the WHERE clause to limit your result set; such a filter is a post-join filter.

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

Figure 2: Query 2

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;

The next example shows the Informix®-extension syntax that us equivalent to the previous ANSI-compliant left outer join:

Figure 3: Query 3

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 
   AND u.customer_num IS NULL;

Here the WHERE clause defines the join condition, and excludes rows with non-NULL cust_calls values.

Examples of ISO/ANSI LEFT OUTER JOIN queries

The following examples illustrate various query constructions that ANSI join syntax can support:

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);

SELECT * FROM
  ((SELECT c1,c2 FROM t3) AS vt3(v31,v32)
    LEFT OUTER JOIN
        ( (SELECT c1,c2 FROM t1) AS vt1(vc1,vc2)
        LEFT OUTER JOIN
        (SELECT c1,c2 FROM t2) AS vt2(vc3,vc4)
         ON vt1.vc1 = vt2.vc3)
ON vt3.v31 = vt2.vc3);

The last example above includes ANSI-compliant joins on derived tables. It specifies a left outer join on the results of a subquery in the FROM clause of the outer query with the results of another left outer join on two other subquery results. See the section Subqueries in the FROM clause for less complex examples of the ANSI-compliant syntax for subqueries.