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.
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.