Data Management

Oracle9i simplifies SQL syntax

Oracle9i SQL includes major improvements over previous versions. Learn more with a detailed discussion of the enhancements, including sample code.


Oracle has made some important enhancements to Oracle9i SQL, including a host of exciting new execution plans, support for scalar subqueries, and support for the ISO 99 SQL standard. As implemented by Oracle, this includes the following new TABLE JOIN syntax:
  • CROSS JOIN—This creates a Cartesian product of the rows in both tables, just like in Oracle8i when the WHERE clause is forgotten.
  • NATURAL JOIN—This is a useful Oracle9i syntax feature that improves the readability of SQL by removing join criteria from the WHERE clause.
  • The USING clause—This allows you to specify the join key by name.
  • The ON clause—This syntax allows you to specify the column names for join keys in both tables.
  • LEFT OUTER JOIN—This returns all the rows from the table on the left side of the join, along with the values from the right-hand side, or nulls if a matching row doesn't exist.
  • RIGHT OUTER JOIN—This returns all the rows from the table on the right side of the join, along with the values from the left-hand side, or nulls if a matching row doesn't exist.
  • FULL OUTER JOIN—This returns all rows from both tables, filling in any blanks with nulls. There is no equivalent for this in Oracle8i.

Most of these enhancements were introduced to allow non-Oracle applications to quickly port onto an Oracle database, and it's important to remember that these are just syntax differences. The ISO 99 standard doesn’t bring any new functionality to Oracle9i SQL.

Want more Oracle? Check out these articles:


The CROSS JOIN
In Oracle, the CROSS JOIN syntax produces a Cartesian product, very much the same as forgetting to add a WHERE clause when joining two tables:
select last_name, dept_id
from emp, depts;

In Oracle9i, we can use the CROSS JOIN syntax to achieve the same result:
select last_name, dept_id
from emp
CROSS JOIN dept;

The NATURAL JOIN
I like the NATURAL JOIN syntax because it automatically detects the join keys, based on the name of the matching column in both tables. This simplifies Oracle9i SQL because the WHERE clause will only contain filtering predicates. Of course, the use of NATURAL JOIN requires that both columns have identical names in each table. It‘s interesting to note that this feature works even without primary or foreign key referential integrity.

Oracle8i
Select book_title, sum(quantity)

From book, sales
Where book.book_id = sales.book_id
group by book_title;

Oracle9i
Select book_title, sum(quantity)

from book
natural join sales
group by book_title;

The USING clause
The USING clause is used if several columns share the same name but you don’t want to join using all of these common columns. The columns listed in the USING clause can’t have any qualifiers in the statement, including the WHERE clause:

Oracle8i
select dept_id, city

from departments, locations
where departments.location_id = location.location_id;

Oracle9i
select department_name, city

from departments
JOIN locations
USING (location_id);

The ON clause
The ON clause is used to join tables where the column names don’t match in both tables. The join conditions are removed from the filter conditions in the WHERE clause:

Oracle8i
select department_name, city

from department, location
where department.location_id = location.loc_id;

Oracle9i
select department_name, city

from department d
JOIN location l
ON (d.location_id = l.id);

Mutable joins
Mutable joins are those where more than two tables are joined. The ISO SQL 1999 standard always assumes the tables are joined from the left to the right, with the join conditions only being able to reference columns relating to the current join and any previous joins to the left:

Oracle8i
select emp_id, city_name, dept_name

from location l, department d, emp e
where d.location_id = l.location_id
and d.department_id = e.department_id;

Oracle9i
select emp_id, city_name, dept_name

from locations l
JOIN departments d ON (d.location_id = l.location_id)
JOIN employees e   ON (d.department_id = e.department_id);

New OUTER JOIN syntax
The ISO 99 standard removes the onerous plus sign (+) from Oracle outer joins and makes outer join SQL easier to understand.

LEFT OUTER JOIN
In a LEFT OUTER JOIN, all rows in the left-hand table are returned, even if there is no matching column in the joined tables. In this example, all employee last names are returned, even those employees who are not yet assigned to a department:

Oracle8i
select last_name, dept_id

from emp  e, dept d
where e.department_id = d.department_id(+);

Oracle9i
select last_name, dept_id

from emp
LEFT OUTER JOIN Dept
ON e.dept_id = d.dept_id;

RIGHT OUTER JOIN
In a RIGHT OUTER JOIN, all rows in the right-hand table are returned, even if there is no matching column in the joined tables. In this example, all department IDs are returned, even for those departments without any employees:

Oracle8i
select last_name, d.dept_id

from employees e, departments d
where e.department_id(+) = d.department_id;

Oracle9i
select last_name, d.dept_id

from employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);

Join the party
The ISO 99 standard is another example of Oracle’s commitment to enhancing its implementation of SQL. The most popular of these enhancements will be the NATURAL JOIN, which simplifies SQL syntax, and the LEFT OUTER JOIN and RIGHT OUTER JOIN, which eliminate the need for the clumsy (+) syntax.

Editor's Picks

Free Newsletters, In your Inbox