Oracle simplifies SQL with ISO 99 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.
 

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, d.department_id
from employees e, departments d;

In Oracle9i, we can use the CROSS JOIN syntax to achieve the same result:

select last_name, d.department_id
from employees e
CROSS JOIN departments d;

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 department_name, city
from departments, locations
where departments.location_id = locations.location_id;

Oracle9i and beyond
 
select department_name, city
from departments
JOIN locations
USING (location_id);
 

select department_name, city
from departments, locations
where department.location_id = locations.location_id;

Oracle9i

select department_name, city
from departments d
join locations l
on (d.location_id = l.location_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 employee_id, city, department_name
from locations l, departments d, employees e
where d.location_id = l.location_id
and d.department_id = e.department_id;

Oracle9i

select employee_id, city, department_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, d.department_id
from employees e, departments d
where e.department_id = d.department_id(+);

Oracle9i

select last_name, d.department_id
from employees e
LEFT OUTER JOIN Departments d
ON e.department_id = d.department_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.department_id
from employees e, departments d
where e.department_id (+) = d.department_id;

Oracle9i

select last_name, d.department_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.

SQL 99 References:

See my related  notes on the ISO SQL 99 Syntax: