Database Fundamentals #22: Using the Join Operator, CROSS JOIN

While the CROSS JOIN is not used much, and, depending on the size of your data it can be dangerous, there are some uses for it. For example, you may want to write a query that will summarize all the sales for the companies and people in your system. You can do this using what is called an aggregate operation or a GROUP BY:

SELECT c.CompanyName,
	p.LastName,
SUM(ft.TransactionAmount) AS 'TransactionTotals'
FROM Management.Company AS c
JOIN Finance.FinancialTransaction AS ft
	ON c.CompanyID = ft.CompanyID
JOIN Personnel.Person AS p
	ON p.PersonID = ft.PersonID
GROUP BY c.CompanyName, p.LastName;

This will add all the values up in the SUM operation for each company and each person that has values so that your data will look like this:

The only problem with this is, you can’t see the any values for the companies and people that have no values in the Finance.FinancialTransaction table. But they’re in your system, so you’d want to see that information. The way to do this would be to combine all the Personnel.Person values with the Mangement.Company values and then go for the aggregation. Here is an example that does just that:

WITH CnP AS
(SELECT p.PersonID,
	c.CompanyID,
	p.LastName,
	c.CompanyName
FROM Management.Company AS c
CROSS JOIN Personnel.Person AS p)

SELECT Cnp.CompanyName,
	CnP.LastName,
	COALESCE(SUM(ft.TransactionAmount), 0) AS 'TransactionTotals'
FROM CnP
LEFT JOIN Finance.FinancialTransaction AS ft
ON CnP.CompanyID = ft.CompanyID
	AND CnP.PersonID = ft.PersonID
GROUP BY CnP.CompanyName, CnP.LastName;

The CROSS JOIN is used in the CTE to define the combination of all companies and all people in the system. Then, with that combination it was possible to do a LEFT JOIN with the Finance.FinancialTransaction table and aggregate to get the transaction totals for all sets of people and companies. The data would look something like this:

But the number of times where you need to put together this type of combination is somewhat limited. You’re much more likely to stick to the other JOIN types most of the time. One other way to create a CROSS JOIN is to completely leave off all JOIN criteria completely. If you wanted to rewrite the CTE in the example above, you could do it like this:

SELECT p.PersonID,
	c.CompanyID,
	p.LastName,
	c.CompanyName
FROM Management.Company AS c,
	Personnel.Person AS p;

While it will work, it’s not as readable and could lead to confusion. You’re better off making things very clear with the CROSS JOIN operator.

Conclusion

This post concludes the fundamentals on the standard JOIN operations. You have now seen INNER, OUTER and CROSS, which covers most situations. The next fundamentals post will be on the WHERE clause.