New Oracle9i ISO 99 joins   Donald K. Burleson

Oracle9i now supports the ANSI/ISO SQL: 1999 standard syntax for joins.  ISO 99 supports cross-joins, natural joins and special syntax for outer joins.

The cross-join is not a very useful enhancement, and I have never seen a use for it.  It is analogous to joining a table, and forgetting the WHERE clause.  It produces a Cartesian product of all rows in both tables:

SELECT

   first_name,last_name,

   department_name
FROM

   employees,

   departments;

In Oracle9i, you can do this with the CROSS JOIN syntax.

SELECT

   first_name,

   last_name,

   department_name
FROM

   employees

CROSS JOIN

   departments;

The NATURAL JOIN performs a join for all columns with matching names in the two tables.  This one is a very useful syntax enhancement because it removes the need to add a WHERE clause for the table join conditions, and make all entries in the WHERE clause only used for result set filtering.  By the way, Oracle9i assumes a join on the columns that have the same name, in the example below, dept_id:

Oracle8i

SELECT

   first_name,

   last_name,

   department_name
FROM

   employees e

   departments d

WHERE

   e.dept_id = d.dept_id;

SELECT

   first_name,

   last_name,

   department_name
FROM

   employees

NATURAL JOIN

   departments;

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.