What Is A Join: Part 3 (Cartesian Joins)

Cartesian Joins (Cross Joins)

This is part 3 of a multi-part series on the SQL Join. Last time I talked about the most common type of join, the Equi-Join (see: What is a JOIN: Part 2 (Inner Join)). This time, I'm going to talk about the least common join type: the Cartesian or Cross Join.

Cartesian Joins are notable mostly for being avoided. A Cartesian Join matches every record in one table to every record in another. Except in very rare instances, this is something to be avoided at all costs. Since it joins all records from both tables, the resultset of a Cartesian join can quickly become huge. A Cartesian join of two tables, each holding a thousand records will result in a query displaying a million records (1000 x 1000). Cross-joining two tables with a hundred thousand records each will exceed the 2GB limit of Access.

A Cartesian join is most commonly created in the Query Builder by neglecting to add a join line between the tables.

Since Orders has 4 records and OrderDetails has 7 records, the resultset of the Cartesian join will have 28 records:

Cartesian Join
OrderNumber	OrderID	Quantity
111	1	1
222	1	1
333	1	1
444	1	1
111	2	2
222	2	2
333	2	2
444	2	2
111	3	3
222	3	3
333	3	3
444	3	3
111	1	2
222	1	2
333	1	2
444	1	2
111	1	1
222	1	1
333	1	1
444	1	1
111	3	2
222	3	2
333	3	2
444	3	2
111	3	1
222	3	1
333	3	1
444	3	1

 

As you can see, the information is fairly useless.

The corresponding SQL statement (SQL View) looks like this:

SELECT Orders.OrderNumber, OrderDetails.OrderID, OrderDetails.Quantity
FROM Orders, OrderDetails;

There is no Join clause between the tables or Join criteria, simply a comma between the tables.

But as I said, there are some rare uses for a Cartesian join.

One almost trivial use is to add a value to each record of a table. For instance, suppose I have a table called User with one record.

User

UserName

Roger

 

If I create a Cartesian join with my Orders table, I'll get:

Cartesian Join 2
OrderID	CustomerID	OrderNumber	UserName
1	1	111	Roger
2	2	222	Roger
3	3	333	Roger
4	1	444	Roger

 

Now, of course there are other ways to do this. However, the Cartesian join has the additional property of being non-updateable. If this is a desirable effect, then a Cartesian join can be useful.

But the main use of a Cartesian join is in creating joins in the Where clause, which I'll talk about in What is a Join: Part 4 (Equi-Joins in the WHERE Clause) .

[top]