Databases Reference
In-Depth Information
Many times, whether to use LEFT OUTER JOIN or RIGHT OUTER JOIN is sim-
ply a matter of style. As you can see, the two previous queries read differently but
produce the same results.
Full Outer Join
Speaking of style and readability, the syntax for a full outer join in Oracle9
i
is greatly
simplified compared to how a full outer join is performed in previous versions of
Oracle. Rather than performing a UNION operation between two distinct queries, the
FULL OUTER JOIN clause is specified between the two tables to be joined.
Janice is cleaning up the rest of her queries to take advantage of the new syn-
tax, and she starts with the UNION query she wrote to display all employees and
all departments in a single query. Here is the original query:
select e.employee_id "Emp ID", e.last_name || ', ' ||
e.first_name "Name", d.department_name "Dept"
from employees e,departments d
where e.department_id(+) = d.department_id
union
select e.employee_id "Emp ID", e.last_name || ', ' ||
e.first_name "Name", d.department_name "Dept"
from employees e,departments d
where e.department_id = d.department_id(+);
In its new format, it ends up a lot shorter and a lot more readable:
select e.employee_id "Emp ID", e.last_name || ', ' ||
e.first_name "Name", d.department_name "Dept"
from employees e
full outer join
departments d
on e.department_id = d.department_id;
Emp ID Name Dept
------- ------------------------- ----------------------
200 Whalen, Jennifer Administration
202 Fay, Pat Marketing
201 Hartstein, Michael Marketing
...
, Corporate Tax
, Construction
, Contracting
, IT Helpdesk
123 rows selected.
