A while back I was sharing some of my favorite SQL Developer interview questions. The one I shared was about the different kinds of JOINS operations you can do in any SQL relational engine. We talked about INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN and FULL OUTER JOIN. One of the joins I left out of the basic set was the CROSS JOIN.
A CROSS JOIN produces a Cartesian product where every record in table a is joined to every record in table b. There is no join criteria specified. You can do it the using older SQL syntax by simply naming the two tables separated by a comma.
SELECT …
FROM TableA, TableB
In the old days this was how you built a query. If you wanted join criteria you included it in the WHERE clause.
Using the more modern syntax you would write the query as follows.
SELECT …
FROM TableA
CROSS JOIN TableB
The advantage of this syntax is that it specifies exactly what your intention. You as stating outright that you want to return a Cartesian product, not that you forgot something in the WHERE clause. As your query becomes more complicated with more tables involved, it is easy to discern what tables are part of the Cartesian product, and what tables are using a different JOIN operation.
The CROSS JOIN is extremely useful in many different situations. It is great when you want to have a list of every possible combination of a pair of lookup tables. Let’s say you want a list of every product sold by region of the company. This CROSS JOIN product against the product and region table, which have nothing in common, allows you to produce a comprehensive list, and then outer join that to the sales table so that all product sales are computed for all regions, even if a product has no sales in one or more regions.
Be wary of the CROSS JOIN. It can have performance issues, because it is a Cartesian product. I can remember having to pull the power cord out of a server 20+ years ago because someone wrote a Cartesian query against to giant tables. It simply ran out of memory, and was overwhelmed with swapping etc.
So, now you know the CROSS JOIN. Go nuts.
Cheers,
Ben