DBA Tips Archive for Oracle


Oracle SQL:99 JOIN Syntax in Oracle9i

by Jeff Hunter, Sr. Database Administrator


  1. ISO/ANSI Standardization
  2. Joins
  3. SQL Scripts

ISO/ANSI Standardization

The Structured Query Language (SQL) is widely recognized as the most portable way of requesting information from relational databases, and is used as one of the main access routes for all such databases, and for certain classes of object oriented databases. The first SQL standard was adopted by ANSI in 1986. In 1993, the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO) development committees decided to split future development into a multi-part standard. Currently, there are five approved standards in the series.

These two main standards bodies are responsible for the enhancement of the SQL language on a regular basis. The most recent official release of the SQL language is call SQL:99, and it was officially adopted in July 1999. SQL:99 represents both the ISO/IEC 9075-1:1999 and ANSI x3.135-1999 standards. Oracle9i introduces a number of enhancements to its product set that implements the SQL:99 standards. Some of which are as follows:

It is important to take advantage of the new SQL:99 syntax in Oracle9i as it provides a seamless way to exchange SQL queries with other databases that are SQL:99 compliant, plus you are explicitly using the keyword JOIN to specify the join operation.


SQL Scripts

SQL Scripts
SQL script that can be used to create all tables used in this example document.
Example SQL query that demonstrates performing a CROSS JOIN in Oracle9i and prior.
Example SQL query that demonstrates performing an equijoin using the NATURAL JOIN keyword.
Example SQL query that demonstrates performing an equijoin using the USING keyword.
Example SQL query that demonstrates performing join operations using the ON clause.
Example SQL query that demonstrates performing outer join operations using the OUTER JOIN syntax.

Copyright (c) 1998-2019 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Thursday, 18-Nov-2010 18:34:40 EST
Page Count: 38679