Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted May 20, 2003

Getting ANSI About Joins

By Jim Czuprynski

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.



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date