Mastering Oracle SQL, 2nd Edition

Appendix A. Oracle’s Old Join Syntax

The join syntax (involving the JOIN, INNER, OUTER, CROSS, LEFT, RIGHT, FULL, ON, and USING keywords) discussed in Chapter 3 was introduced in Oracle9i Database to make Oracle’s join functionality compliant with the ANSI/ISO SQL92 standard known as SQL92. Prior to Oracle9i Database, Oracle supported the join syntax defined in the SQL86 standard. In addition, also prior to Oracle9i Database, Oracle supported outer joins through a proprietary outer join operator. Even though the new SQL92 join syntax is more elegant and powerful, the old join syntax and the proprietary outer join operator are still supported in Oracle Database 10g, for backward compatibility.

If you are writing a new application, we highly recommend that you use the SQL92 join syntax. However, if you have a pre-Oracle9i Database application, you need to understand both syntaxes—the old and the new. In this appendix, we illustrate the old join syntax, and show how it relates to the new syntax. This will help you to migrate an application from the old syntax to the new syntax, and it will help you when you are faced with maintaining an older application.

Old Inner Join Syntax

The following example illustrates the older inner join syntax:

SELECT d.name, l.regional_group
FROM department d, location l
WHERE d.location_id = l.location_id;

The corresponding query with the new syntax is:

SELECT d.name, l.regional_group
FROM department d JOIN location l
ON d.location_id = l.location_id;

Following ...