Progress
SQL-92
Guide and Reference


Outer Joins

An outer join between two tables returns more information than a corresponding inner join. An outer join returns a result table that contains all the rows from one of the tables even if there is no row in the other table that satisfies the join condition.

In a left outer join, the information from the table on the left is preserved: the result table contains all rows from the left table even if some rows do not have matching rows in the right table. Where there are no matching rows in the right table, SQL generates null values.

In a right outer join, the information from the table on the right is preserved. The result table contains all rows from the right table even if some rows do not have matching rows in the left table. Where there are no matching rows in the left table, SQL generates null values.

SQL uses two forms of syntax to support outer joins:

NOTE: The Progress implementation of syntax for a where_clause_outer_join does not comply with the ANSI SQL-92 standard. The alternate syntax will not operate properly for clients requiring SQL-92 standard syntax.

This is the syntax for a from_clause_outer_join:

SYNTAX
FROM table_ref LEFT OUTER JOIN table_ref
  ON search_condition 

This is the syntax for a where_clause_outer_join:

SYNTAX
WHERE [ table_name.] column (+) = [ table_name.]column
  | WHERE [ table_name.]column = [ table_name.]column (+) 

EXAMPLE

The following example shows a left outer join. The query requests information about all the customers and their orders. Even if there is not a corresponding row in the orders table for each row in the customer table, NULL values are displayed for the order.order_no and order.order_date columns:

SELECT customer.cust_no, customer.name, orders.order_no, orders.order_date
     FROM customers, orders
     WHERE customer.cust_no = orders.cust_no (+) ; 

The following queries illustrate outer join syntax:

SELECT * FROM T1; -- Contents of T1
C1  C2 
--  --  
10  15 
20  25 
2 records selected
 
 
SELECT * FROM T2; -- Contents of T2
C3  C4 
--  --  
10  BB 
15  DD 
2 records selected
 
 
-- Left outer join
SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.C1 = T2.C3;
C1  C2  C3  C4 
--  --  --  --  
10  15  10  BB 
20  25      
2 records selected
 
 
-- Left outer join: different formulation, same results
SELECT * FROM T1, T2 WHERE T1.C1 = T2.C3 (+);
C1  C2  C3  C4 
--  --  --  --  
10  15  10  BB 
20  25      
2 records selected
 
 
-- Right outer join
SELECT * FROM T1, T2 WHERE T1.C1 (+) = T2.C3; 
C1  C2  C3  C4 
--  --  --  --  
10  15  10  BB 
        15  DD 
2 records selected 


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095