Mastering Oracle SQL

ANSI-Standard Join Syntax in Oracle9i

Oracle9i introduced new join syntax that is compliant to the ANSI SQL standard defined for SQL/92. Prior to Oracle9i, Oracle supported the join syntax defined in the SQL/86 standard. In addition, Oracle supported outer joins through the proprietary outer join operator (+), discussed earlier in this chapter. The old join syntax and the proprietary outer join operator are still supported in Oracle9i. However, the ANSI standard join syntax introduces several new keywords and new ways to specify joins and join conditions.

New Join Syntax

With the traditional join syntax, you specify multiple tables in the FROM clause separated by commas, as in the following example:

SELECT L.LOCATION_ID, D.NAME, L.REGIONAL_GROUP
FROM DEPARTMENT D, LOCATION L
WHERE D.LOCATION_ID = L.LOCATION_ID;

With the new syntax in Oracle9i, you specify the join type with the JOIN keyword in the FROM clause. For example, to perform an inner join between tables DEPARTMENT and LOCATION, you specify:

FROM DEPARTMENT D INNER JOIN LOCATION L

In the traditional join syntax, the join condition is specified in the WHERE clause. With the new syntax in Oracle9i, the purpose of the WHERE clause is for filtering only. The join condition is separated from the WHERE clause and put in a new ON clause, which appears as part of the FROM clause. The join condition of the previous example will be specified using the new syntax as:

ON D.LOCATION_ID = L.LOCATION_ID;

The complete join, using the new ...