Cartesian Query

When creating a query involving multiple tables, we can select from three types of table joins depending on the desired result of the query. Usually, we want the query to return only the matching records between the tables, in which case an “inner join” would be used.

Sometimes, the desired result is for the query to return all the records from one table with the matching records from the related table. In these cases, an “outer join” (left or right, Access does not support “full outer join”) would be the join of choice.
However, there will be times when none of these join types will return the desired result. In which case, the only thing left to do is to not use a join at all. That’s when you get a ”Cartesian Query.”

Basically, a Cartesian Query returns a set of data that include all the records from all the tables in the query with a slight difference, the number of records returned will be the cartesian product between the number of records from all the tables.
What exactly does that mean? To better illustrate, take a look at the following sample data.

Table 1

Table 2

Cartesian Query

As you can see, the query returned as many records in Table2 for each record in Table1 (3 x 3 = 9). Here’s the SQL statement used to create that query:

SELECT Table1.Field1, Table1.Field2, Table2.Field1, Table2.Field2
FROM Table1, Table2;

You will notice that there was no relationship join used between Table1 and Table2.

So, why would anyone want to use a Cartesian Query? Believe it or not, there are many valid reasons for using a Cartesian Query. For example, you can use a Cartesian Query to print a shipping label to indicate the box or pallet number within the number of boxes or pallets per shipment, or to display all possible network addresses within an IP range, or to calculate the fractional cost of a product over its life expectancy, or to generate a daily roster for student attendance or employee training or meeting.

One last note to remember when using a Cartesian Query: the resulting data set will not be updateable. So, the result of Cartesian Queries is usually used only as a data source for reports or other queries.

Hopefully, this post helped you become a little bit familiar with Cartesian Queries so that maybe next time when you need to generate records that are not normally stored in your table, you won’t be afraid to try it. Who knows, using a Cartesian Query could provide the solution you were looking for.

In my next post, I will show you one practical use of a Cartesian Query by generating all the calendar dates within a user defined range. See you next time…

Original post date: January 20, 2013