SQL Reference Guide

Outer Joins

Description

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 left table, FairCom DB 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 right table, FairCom DB SQL generates null values.

FairCom DB SQL supports two forms of syntax to support outer joins:

• In the FROM clause, specify one of the outer join clauses between two table names, followed by a search condition:

  LEFT OUTER JOIN

  RIGHT OUTER JOIN

  The search condition can contain only the join condition between the specified tables.

• In the WHERE clause of a query expression, specify the outer join operator (+) after the column name of the table for which rows will not be preserved in the result table. Both sides of an outer-join search condition in a WHERE clause must be simple column references. This syntax allows both left and right outer joins.

FairCom DB SQL does not support full (two-sided) outer joins.

Syntax

from_clause_inner_join ::

FROM table_ref LEFT OUTER JOIN table_ref ON search_condition

| FROM table_ref RIGHT OUTER JOIN table_ref ON search_condition

where_clause_inner_join ::

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

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

Examples

The following example shows a left outer join. It displays all the customers with 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 orders.order_no and orders.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 series of examples illustrates the 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 RIGHT OUTER JOIN T2 ON T1.C1 = T2.C3;

C1 C2 C3 C4

-- -- -- --

10 15 10 BB

15 DD

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