Wednesday, August 19, 2009

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]

1 comment:

Bert Malliet said...

I find a cartesian join very usefull, in combination with the "like" operator, for matching tables where information from a field of table A is "buried somewhere" in a field of table B. Example: match Orders (A) to bankaccount Transactions (B). SQL: insert into OrderTransaction ... select ... from Order, Transaction where Transaction.Communication like "*" & Order.Order_Id & "*".