Outer Joins
Data can be combined from two or more tables to produce an intermediate results table using an outer join.
Note: Outer join functionality is available only if OUTER_JOIN is set to Y in the iidbcapabilities table.
Note: Outer joins specified in the FROM clause are not the same as joins specified in the WHERE clause: the FROM clause specifies sources of data, while the WHERE clause specifies restrictions to be applied to the sources of data to produce the results table.
Outer joins are specified in the FROM clause, using the following syntax:
source join_type join source
on search_condition
where:
• The source parameter is the table, view, or outer join where the data for the left or right side of the join originates.
• The join_type parameter specifies INNER, LEFT, RIGHT, or FULL outer join. The default join type is INNER.
• The search_condition is a valid restriction, subject to the rules for the WHERE clause. The search condition must not include aggregate functions or subselects.
Think of an outer join is as the union of two SELECT statements: the first query returns rows that fulfill the join condition, and the second returns nulls for rows that do not.
There are three types of outer joins:
• Left outer join - Returns all values from the left source
• Right outer join - Returns all values from the right source
• Full outer join - Returns all values from both sources
Note: Right and left joins are symmetrical: (table1 right-join table2) returns the same results as (table2 left-join table1).
By default, joins are evaluated left to right. To override the default order of evaluation, use parentheses.
A source can itself be an outer join, and the results of joins can be joined with the results of other joins, as illustrated in the following pseudocode:
(A join B) join (C join D)
The placement of restrictions is important in obtaining correct results. For example:
A join B on cond1 and cond2
does not return the same results as:
A join B on cond1 where cond2
In the first example, the restriction determines which rows in the join result table will be assigned null values; in the second example, the restriction determines which rows will be omitted from the result table.
The following example uses an outer join in the FROM clause to display all employees along with the name of their department, if any:
select e.ename, d.dname from
(employees e left join departments d
on e.edept = d.ddept);
Limitations and Restrictions on OUTER JOIN and NATURAL JOIN
OUTER JOIN is a subset of the ANSI SQL92 JOIN statement. It includes LEFT, RIGHT, and FULL JOIN. ANSI JOIN also includes:
• NATURAL [INNER] JOIN
• NATURAL LEFT [OUTER] JOIN
• NATURAL RIGHT [OUTER] JOIN
• NATURAL FULL [OUTER] JOIN
The keyword OUTER is optional for Ingres, SQL Server, Oracle, and DB2UDB databases.
At this time, only Oracle has implemented NATURAL JOIN. Because Ingres does not support NATURAL JOIN, it is not part of OpenSQL. Until Ingres supports it, NATURAL JOIN will not be implemented in gateways.
Oracle’s OUTER JOIN syntax is similar to that of Ingres.
USING Clause Support
Because SQL Server and DB2UDB do not support the USING clause in outer joins, USING clauses are passed through, and it is left to the underlying DBMS to determine whether the statement can be executed. If the database cannot execute the statement, it should report an error.