Getting ANSI About Joins

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.