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:
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
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.