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":

Example: NATURAL Join

Traditional
Syntax

ANSI
SYNTAX

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.

Jim Czuprynski
Jim Czuprynski
Jim Czuprynski has accumulated over 30 years of experience during his information technology career. He has filled diverse roles at several Fortune 1000 companies in those three decades - mainframe programmer, applications developer, business analyst, and project manager - before becoming an Oracle database administrator in 2001. He currently holds OCP certification for Oracle 9i, 10g and 11g. Jim teaches the core Oracle University database administration courses on behalf of Oracle and its Education Partners throughout the United States and Canada, instructing several hundred Oracle DBAs since 2005. He was selected as Oracle Education Partner Instructor of the Year in 2009. Jim resides in Bartlett, Illinois, USA with his wife Ruth, whose career as a project manager and software quality assurance manager for a multinational insurance company makes for interesting marital discussions. He enjoys cross-country skiing, biking, bird watching, and writing about his life experiences in the field of information technology.

Latest Articles