Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Redneck
 

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:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

 

”call

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 
   

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

Oracle® is the registered trademark of Oracle Corporation.