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 - Page 3

By Jim Czuprynski

by Jim Czuprynski

Cartesian Products. Though I must admit I have spent a lot of time trying to avoid them, Cartesian products do happen from time to time, usually as the result of an equijoin condition that has been missed in a query using traditional syntax. However, one of the advantages of the ANSI join syntax is that a specific keyword, CROSS JOIN, is required to create a Cartesian product, as shown in these next examples:

Example: CROSS JOIN

Traditional Syntax

ANSI SYNTAX

SELECT 
   d.department_name,
   l.city,
   l.country_id
  FROM
    departments d,
    locations l
 WHERE l.country_id = 'JP';
SELECT 
   d.department_name,
   l.city,
   l.country_id
  FROM departments d
    CROSS JOIN locations l
 WHERE l.country_id = 'JP';

Summary. Though my experiments are still underway, and I have yet to completely convince my fellow developers of the benefits of the ANSI syntax, I have found that there is indeed considerable value in ANSI join syntax:

Intuitive. The ANSI syntax uses specific, intuitive keywords to describe the exact operations to be performed while the sets of data are being joined.

Distinctive. Since the new ANSI syntax is quite different from the traditional join method, it is immediately obvious which sets of data have been joined and what the relationships are. It is no longer necessary to untangle a complex set of selection criteria to determine which are join-related and which are merely filtering the final result set.

Clarity. I have had to work with several different dialects of SQL over the past several years, including Informix, Sybase, DB2, and Access, and the syntax for outer joins is slightly different for each dialect. The ANSI syntax, however, leaves no doubt as to which "side" of the join is the outermost.

More powerful. The new FULL OUTER JOIN syntax can be used in place of a UNION without fear of forgetting the difference between UNION and UNION ALL.

If you still harbor any doubts about experimenting with and implementing the new ANSI syntax, consider this: Oracle mentions in the preparation notes for the first OCA test that extensive knowledge of the ANSI join syntax is important for a passing grade. If you are on the road to becoming an Oracle Certified Professional, it certainly appears they must be mastered.

Jim Czuprynski is an Oracle DBA for a telecommunications company in Schaumburg, IL. He can be contacted at jczuprynski@zerodefectcomputing.com.


Example Tables Metadata. Here's a listing of all tables and their corresponding columns in Oracle's HR example schema.

SQL> select table_name from user_tables;

TABLE_NAME                                                                      
------------------------------                                                  
COUNTRIES                                                                       
DEPARTMENTS                                                                     
EMPLOYEES                                                                       
JOBS                                                                            
JOB_HISTORY                                                                     
LOCATIONS                                                                       
REGIONS                                                                         

7 rows selected.

SQL> describe countries
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COUNTRY_ID                                NOT NULL CHAR(2)
 COUNTRY_NAME                                       VARCHAR2(40)
 REGION_ID                                          NUMBER

SQL> describe departments
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPARTMENT_ID                             NOT NULL NUMBER(4)
 DEPARTMENT_NAME                           NOT NULL VARCHAR2(30)
 MANAGER_ID                                         NUMBER(6)
 LOCATION_ID                                        NUMBER(4)

SQL> describe employees
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

SQL> describe jobs
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 JOB_ID                                    NOT NULL VARCHAR2(10)
 JOB_TITLE                                 NOT NULL VARCHAR2(35)
 MIN_SALARY                                         NUMBER(6)
 MAX_SALARY                                         NUMBER(6)

SQL> describe job_history
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 START_DATE                                NOT NULL DATE
 END_DATE                                  NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 DEPARTMENT_ID                                      NUMBER(4)

SQL> describe locations
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 LOCATION_ID                               NOT NULL NUMBER(4)
 STREET_ADDRESS                                     VARCHAR2(40)
 POSTAL_CODE                                        VARCHAR2(12)
 CITY                                      NOT NULL VARCHAR2(30)
 STATE_PROVINCE                                     VARCHAR2(25)
 COUNTRY_ID                                         CHAR(2)

SQL> 
SQL> describe regions
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 REGION_ID                                 NOT NULL NUMBER
 REGION_NAME                                        VARCHAR2(25)




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