Simple outer join on two tables

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

Figure 1: Query

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 the result shows.

Figure 2: Query result

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