5.5. Cartesian Products: The Black Sheep of the Family

What if you were joining two tables, or even three tables, and you left off the join conditions? The result would be a Cartesian product. Every row of each table in the FROM clause would be joined with every row of the other tables. If one table had 15 rows, and a second table had 21 rows, a Cartesian product of those two tables would produce 315 rows in the result set of the query. Needless to say, it can be a big problem when you have three or more tables with no join conditions specified.

Cartesian product

A join between two tables where no join condition is specified, and as a result, every row in the first table is joined with every row in the second table.

Needless to say, Cartesian products are used quite infrequently in SELECT statements, but they can be useful in very specific situations. For example, a Cartesian product of the EMPLOYEES table and the COUNTRIES table could give Janice a way to produce a checklist in a spreadsheet to note when a particular employee has visited one of the countries where Scott's widget company has a field office or distribution center. If employee visits to other offices were tallied in another table, then the Cartesian product could be joined to the new table as a running total of visits by employees to other offices.

5.5.1. Pre-Oracle9i Cartesian Product Syntax

Janice decides that the employee/country visit idea has some merit, and she experiments with some queries to generate the combinations of employees and countries using a Cartesian product query:

select e.employee_id "Emp ID", e.last_name "Emp Name",
       c.country_id "Cntry ID", c.country_name "Cntry Name"
from employees e, countries c;

Emp ID Emp Name        Cn Cntry Name
---------- --------------- -- --------------------
       100 King            AR Argentina
       101 Kochhar         AR Argentina
       102 De Haan         AR Argentina
       103 Hunold          AR Argentina
...
       201 Hartstein       ZW Zimbabwe
       202 Fay             ZW Zimbabwe
       203 Mavris          ZW Zimbabwe
       204 Baer            ZW Zimbabwe
       205 Higgins         ZW Zimbabwe
       206 Gietz           ZW Zimbabwe

2675 rows selected.

5.5.2. Oracle9i Cartesian Product Syntax

The same query using the Oracle9i syntax is similar, except that CROSS JOIN is used to separate the two tables that are queried to produce a Cartesian product. Janice changes the previous query to use the Oracle9i version:

select e.employee_id "Emp ID", e.last_name "Emp Name",
       c.country_id "Cntry ID", c.country_name "Cntry Name"
from employees e cross join countries c;

    Emp ID Emp Name        Cn Cntry Name
---------- --------------- -- --------------------
       100 King            AR Argentina
       101 Kochhar         AR Argentina
       102 De Haan         AR Argentina
       103 Hunold          AR Argentina
...
       201 Hartstein       ZW Zimbabwe
       202 Fay             ZW Zimbabwe
       203 Mavris          ZW Zimbabwe
       204 Baer            ZW Zimbabwe
       205 Higgins         ZW Zimbabwe
       206 Gietz           ZW Zimbabwe

2675 rows selected.