7.4. Cartesian Product Joins

As mentioned previously, the Cartesian product (or cross join) represents all possible combinations of rows and columns from the joined tables. To be exact, it represents the sum of the number of columns of the input tables plus the product of the number of rows of the input tables. Put another way, it represents each row from the first table matched with each possible row from the second table, and so on and so forth. For example, if you performed a joint operation on one table consisting of 100,000 rows and a second table of 10,000 rows, you would get a Cartesian product of 10 million rows.

Although the Cartesian product serves a very useful purpose in the relational model, it is essentially meaningless for a user to intentionally produce it as a final table. Besides being large, Cartesian products contain too much information and make it difficult, if not impossible, for the practitioner to select what is salient. It is only when you subset the Cartesian product using a WHERE clause that your data becomes quantifiable and manageable. For more information on Cartesian Product joins and examples illustrating the results of these joins, go to the Companion Web Site for this book.