Forum Discussion
Fuzzy Logic in PQ
The Cartesian Product
Trying to return data from two or more tables without any joins creates what is called a "Cartesian product." A Cartesian product is defined as all possible combinations of rows in all tables. Be sure you have joins before trying to return data, because a Cartesian product on tables with many records and/or on many tables could take several hours to complete. The following is a Cartesian product as used on the two example tables; note that this table is only 3 records times 3 records, which yields a total of 9 records. However, imagine if instead, the table was 100 records times 1,000 records times 10,000 records; then the table would contain 1,000,000,000 records!
Join_Field Join_Field Color_Field Pattern_Field
1 2 Red Striped
1 3 Red Checkered
1 4 Red Polka-Dot
2 2 Blue Striped
2 3 Blue Checkered
2 4 Blue Polka-Dot
3 2 Green Striped
3 3 Green Checkered
3 4 Green Polka-Dot
Occasionally, some users want to use a Cartesian product; however, most users who get them do so accidentally, and are often confused by them. Because most users exclude most of the fields in a join, a real Cartesian product can easily look as perplexing as this:
Color_Field
Red
Red
Red
Blue
Blue
Blue
Green
Green
Green
If 100 records are added to Pattern_Table, this query would have 309 records (103 records each of Red, Blue, and Green).
Cartesian products have relatively simple SELECT statements. The following is how the SQL might look for the above Cartesian product:
SELECT Color_Table.Color_Field, Pattern_Table.Pattern_Field
FROM `c:\database`.Color_Table, `c:\database`.Pattern_Table