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.

Note

Partial Cartesian products are produced when a query with n tables has less than n-1 join conditions between tables.

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.

Pre-Oracle9i Cartesian Product Syntax

Janice decides that the employee/country visit idea has some merit, and 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.

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.




Oracle9i DBA JumpStart
Oracle9i DBA JumpStart
ISBN: 0782141897
EAN: 2147483647
Year: 2006
Pages: 138
Authors: Bob Bryla

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net