msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

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.


Filed in Joins, Oracle on 16 Mar 06 | Tags: ,


Reader's Comments

  1. |

    I think it’s a big step for most people (including myself) to abandon the old syntax and start using the SQL92 (or is it SQL99??) standards. I haven’t seen it in the wild yet, anyway.

    I’m curious what Oracle’s statement is. Have they included it for compatibilty to newer standards? Or will they push the usage of this syntax?

  2. |

    Patrick, I’m with you. The only time I’ve used the new standards so far was when I needed a ‘full outer join’ in Ansi speak.

    It will take a lot of effort to change and if there’s no tangible benefit then it falls way down my list of priorities.

  3. |

    Well, I really like the explicit ANSI style, and like Eddie I’m using it as my main way of writing joins. I find it a lot clearer, and it’s harder to miss one and get a Cartesian product by mistake.

    I used the other style for well over 10 years before this came along, so I don’t really buy the “but I’m used to the other way” argument.

    Oracle WTF: Joins Explained

  4. |

    My wife Jasmine is a SQL Server developer (Yes, we are an example of Oracle and Microsoft living under the same roof). One additional reason I want to use the ANSI style is to make it easier for me to “communicate” with Jasmine when speaking SQL :)

  5. |

    Very detailled information about sql syntax and sql injection here: http://www.sqlexikon.de, this site is in german, so don’t waste your time, if that language is a problem for you :)

  6. |

    The site quickly helps to improve my knowledge