Getting ANSI About Joins - Page 3

May 20, 2003

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:

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)