Donald K. Burleson Oracle Tips

The new Oracle9i SQL USING and ON clause

As part of the Oracle9i enhancements to ISO 99 SQL, Oracle has added new USING and ON clauses for performing natural joins.  This allows additional flexibility to the developer since they are able to explicitly control the columns that are used in the table join.

The USING clause is used if several columns can be used to join the tables.

Oracle8i

select

   last_name,

   department_name
from

   employees e,

   departments d
where

   e.department_id = d.department_id;

Oracle9i

select

   last_name,

   department_name
from

   employees e,

   departments d
USING (department_id);

The ON clause is used to join tables where the column names do not match in both tables.

Oracle8i

select

   last_name,

   department_name
from

   employees e,

   departments d
where

   e.department_id = d.dept_id;

Oracle9i

select

   last_name,

   department_name
from

   employees e,

   departments d
ON (department_id = dept_id);

It is important to note that the old-fashioned Oracle SQL: join syntax continues to be supported, and this new enhancements if most important for porting non-Oracle systems into an Oracle database.

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.