I have a list of teams and a list of locations with ballparks. I wish to create a master list of all the combinations possible between these two lists.
We typically use queries to limit the amount of returned records, and at the very least, we expect the number of returned records to be no greater than the number of records in the largest table or query being addressed.
However, there is a special type of join, called a Cartesian join, that returns the multiplicative result of the fields in the query (otherwise known as the Cartesian product). A Cartesian join is the antithesis of standard joins—it works as if there is no join. Whereas the other join types link tables together on common fields, no field linking is required to return a Cartesian product.
Figure 2-19 shows a table of teams and a table of locations. Simply put, we are looking for all the combinations that can exist between these two tables.
Figure 2-19. A table of teams and a table of locations
Figure 2-20 shows the design of a query that essentially has no join. There is no line connecting the tables. In fact, the single fields in each table really don't relate to each other.
The SQL generated by the design in Figure 2-20 looks like this:
SELECT Teams1.Team, Locations.Location FROM Teams1, Locations;
Figure 2-20. Design of a Cartesian join
Note that ...