I recently had an opportunity to review a Microsoft Access query created against one of our production databases. The query seemed to run forever and took up huge amounts (4GB) of TEMPFILE space. Our development team eventually solved the problem an inexperienced developer had missed a critical join between one of the six tables in the query. Moreover, since Access translates all its joins into ANSI syntax, it took us some time to decode the SQL that caused the problem.

If you've been working with SQL for some time, I'm willing to bet that the ANSI joins may look a bit confusing and convoluted, especially if you've always used the "traditional" join predicates (i.e. WHERE table1.column = table2.column). I have been writing SQL queries since 1985 starting with TERADATA databases, back when the concept of a terabyte of information was still intimidating. However, I must admit that the ANSI syntax, once understood, has some advantages over the traditional syntax.

Here is a quick summary of how the ANSI syntax can be applied to SQL queries in Oracle 9iR2. I have provided side-by-side examples showing how the traditional vs. ANSI syntax can be used to obtain the same result. Please note that I have utilized the HR schema in the EXAMPLE tablespace that is provided with Oracle 9iR2 for these examples. See the section at the end of this document for a listing of these tables' metadata.

NATURAL Joins. A natural join, as its name implies, can be invoked when two or more tables share exactly the same columns needed for a successful equijoin. For example, these queries will return all Region and Country information for all countries whose name that contains the string "united":

SELECT
   r.region_id,
   r.region_name,
   c.country_id,
   c.country_name
  FROM 
   countries c,
   regions r
 WHERE c.region_id = r.region_id
   AND LOWER(c.country_name) LIKE '%united%';

SELECT
   region_id,
   r.region_name,
   c.country_id,
   c.country_name
  FROM countries c
  NATURAL JOIN regions r
 WHERE LOWER(c.country_name) LIKE '%united%';

Note that even though the table names are prefixed in both examples, REGION_ID cannot use the prefix; Oracle chooses the appropriate column in the naturally-joined table from which to gather data. If you get an ORA-25155: column used in NATURAL join cannot have qualifier error message, check over the columns named in the query you may have inadvertently broken this rule.

Next

[1] [2] [3] Next