Back to basics: cross joins

Am I considered old school because I do not use Oracle’s SQL92 compliant join syntax? After all, it is recommended to follow the standards whenever possible.

Starting with Oracle database version 9i, you can use the SQL join syntax that is compliant with the ANSI/ISO SQL92 standard. You also have the option to continue using the “old” syntax - that I have been using for years and that I am so used to. But to me, one of the main advantages of using the “new” join syntax is that join conditions are separated from the other filter conditions in the WHERE clause, making the query self explanatory as far as join conditions are concerned.

I have decided to break my habit and start using the “new” syntax in all my queries (except when I’m working against Oracle 8i). To that end, I will be writing a series of posts about the different types of joins along with code examples both in the “old” and the corresponding “new” syntax. Nothing revolutionary, just simple, “back to basics” stuff.

Here are the different types of joins that I’m planning to review:

  • Cross joins
  • Inner joins
  • Outer joins
  • Equi-joins and non equi-joins
  • Self joins
  • Anti-joins
  • Semi-joins

I will start with cross joins, a.k.a. Cartesian products. A Cartesian product is returned when you don’t specify a join condition when joining two tables. Oracle combines each row from the first table with each row from the second table. The number of rows in a Cartesian product is equal to the number of rows in the first table multiplied by the number of rows in the second table.

Using the old syntax:

select e.first_name, d.department_name
from employees e, departments d

Using the new syntax:

select e.first_name, d.department_name
from employees e cross join departments d

Result set:

FIRST_NAME           DEPARTMENT_NAME                
-------------------- ------------------------------ 
Ellen                Administration                 
Sundar               Administration                 
Mozhe                Administration                 
...
...
Matthew              Payroll                        
Jennifer             Payroll                        
Eleni                Payroll                        

2889 rows selected

2889 = 107 employees * 27 departments.

Cross joins are usually useless and should be avoided.

Using the old syntax, it is easier to forget to include a join condition resulting in an unintentional Cartesian product.

Always include a join condition unless you specifically need a Cartesian product. Join conditions are included in all the other join types.

In my next post, I will review inner joins.

Possibly related:

Tagged , | Post a Comment