Donald K. Burleson Oracle Tips

New Oracle9i outer joins

Starting with Oracle9i, the confusing outer join syntax using the ‘(+)’ notation has been superceded by ISO 99 outer join syntax.  As we know, there are three types of outer joins, left, right, and full outer join.  The purpose of an outer join is to include non-matching rows, and the outer join returns these missing columns as NULL values. 

Let’s review the syntax differences between these variations in join syntax:

Left outer join:  Oracle8i

select

   last_name,

   department_name
from

   employees e,

   departments d
where

   e.department_id = d.department_id(+);

Left outer join:  Oracle9i

select

   last_name,

   department_name
from

   employees e

left outer join

   departments d

on

   e.department_id = d.department_id;

Right outer join: Oracle8i

select

   last_name,

   department_name
from

   employees e,

   departments d
where

   e.department_id(+) = d.department_id;

Right outer join: Oracle9i

select

   last_name,

   department_name
from

   employees e

right outer join

   departments d

on

   e.department_id = d.department_id;

The full outer join has no direct equivalent in Oracle8i, but it is very handy to find missing rows in both tables being joined.  In the example below, we include employees with departments as well as departments without employees:

select

   last_name,

   department_name

from

   employees e

full outer join

   departments d

on e.department_id = d.department_id;

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

Copyright © 1996 -  2009 by Burleson Enterprises, Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.