Outer Joins
You can combine data from two or more tables to produce an intermediate results table using an outer join.
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
source
Specifies the table, view, or outer join where the data for the left or right side of the join originates
join_type
Specifies an inner, left, right, or full outer join.
Default: inner
search_condition
Specifies a valid restriction, subject to the rules for the where clause. The search condition must not include aggregate functions or subselects.
You may 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
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 you can join the results of joins with the results of other joins, as illustrated in the following pseudo code:
(A join B) join (C join D)
How you place 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 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);