6. MICROSOFT ACCESS QUERIES – ADVANCED
6.8.3: Cartesian Product
Suppose you create a query, but without join criteria. This is easily done by clicking on the relationship line and deleting it. When criteria for matching rows is not present, then each row of one table will join to each row of the other table.
This type of join is called a Cartesian Product, and these can easily have very large result sets. If Department has 4 rows and Employee has 100 rows, then the Cartesian Product has (4×100=) 400 rows. Databases used in practice have hundreds, thousands, even millions of rows; a Cartesian Product may take a long, long time to run.
Exercises
1) Consider the Sales database and its Store and Product tables. Construct a query to list the storeID and the productID. When you add Store and Product to the relationships area there is no line joining the two tables. Run the query. Notice how many rows there are; the number of rows in the result set is the number of stores times the number of products.
2) Consider the Sales database and its Store, Product, and Sales tables. Suppose we want to obtain a list that shows for each store and product the total quantity sold. Note that the end user wants to see every store and product combination.
Hint: An approach you can use with MS Access is to create two queries. The first of these performs a cross product of store and product (call this CP).
The second query is developed as a join between the query CP and the table Sales. CP is outer-joined to Sales in order that every combination of Store and Product is in the final result.
