Cartesian Products: The Black Sheep of the Family - Oracle DBA

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 she experiments with some queries to generate the combinations of employees and countries using a Cartesian product query:

Pre-Oracle9i Cartesian Product Syntax

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:

Oracle9i Cartesian Product Syntax 2675 rows selected.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle DBA Topics