{{announcement.body}}
{{announcement.title}}

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

DZone 's Guide to

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

Learn how to use the join operator and use CROSS JOIN.

· Database Zone ·
Free Resource

Image title

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;
You might also like:  Overview of SQL Joins in SQL Server

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.

Further Reading

SQL Joins (In Postgres)

Database Fundamentals #21: Using the JOIN Operator, OUTER JOIN

Topics:
database ,cross join ,database fundamentals ,join operator ,left join ,inner join ,outer join ,tutorial

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}