Joins are an interesting and critical part of understanding and using the SQL language. One of the most curious is the Cartesian join.

Simply put, cartesian joins generate a “cartesian product”, which is defined as “…the product of two sets: the product of set X and set Y (is) the set that contains all ordered pairs (x, y) for which x belongs to X and y belongs to Y…”.

SQL Server

In SQL Server, the cartesian product is really a cross-join which returns all the rows in all the tables listed in a query:  each row in the first table is paired with all the rows in the second table.  This happens when there is no relationship defined between the two tables.

An Example

To best understand this concept, consider the following example.

In a new fast food restaurant, the manager is keen to assign the duty in which each employee is most efficient performing. A database has been created which contains the following three tables:

C_Employee – holding the restaurant employees

C_Duty – holding the duties

C_Utilization – tracking employee utilization by duty

Currently we have only 3 employees:

And only 3 duties to be performed:

Using a cartesian join, we can write the following query:

Select EmployeeName

,Duty

from

C_Employee, C_Duty

When we run that query, we receive the expected results (the cartesian product):

You can see that we have each duty assigned to each employee for a total of 9 rows (3 employees x 3 duties) returned.

Taking this a step further, we know that in the 3rd table, calculated utilizations for each duty are being entered by Employee ID:

So, if we introduce a subquery into our original query:

Select EmployeeName

,Duty

,(Select Utilization from C_Utilization where

C_Employee.EmployeeID = C_Utilization.EmployeeID

and C_Duty.DutyID = C_Utilization.DutyID) AS ‘UTILIZATION’

from

C_Employee, C_Duty

order by EmployeeName, UTILIZATION DESC

it will add some insight to our results in that we can easily identify which duty is perhaps best for each of our valued employees:

Conclusion

So, a JOIN is a SQL “clause” that is used to bring together rows from two or more SQL tables, based on a related column between them. The cartesian join is just one type of join. You can learn about all of different SQL joins here, or look for my next post that explains the use of each type of join.

I hope this little example helped in understanding how a cartesian join it works.