Query Motivation for Cross Joins

Question

Say we have two tables 'Vendors' and 'Invoices'. Then I understand a reason for doing a left join of 'Vendors' with ' Invoices' along the VendorID column is to obtain information on Vendors without Invoices. A right join is basically a left join with tables interchanged (This is the main motivation,right)?

But I cannot see a general motivation from cross joins in this sense, i.e., if I am given tables Vendors, Invoices joined along VendorID like above, what information is it that Vendors Cross Join allows me to obtain that I cannot obtain through a left join, or otherwise?

Thanks.

Answer 1

CROSS JOINs are generally used to explode a data set in order to produce a large number of rows. For example, I use CROSS JOINs to create my Numbers tables.

There are other practical purposes, though. Let's say you have a table of permissions:

CREATE TABLE dbo.BasePermissions
(
  PermissionID INT PRIMARY KEY,
  Description VARCHAR(32)
);
INSERT dbo.BasePermissions VALUES(1, 'Do stuff'),(2, 'Do other stuff');

Now, you probably have a table mapping UserID -> PermissionID, but this isn't really necessary for admin users. So in that case you might have the following query (let's say the application does need the granular permissions for each user, or you have exceptions for specific users you might apply later, etc.):

SELECT u.UserID, p.PermissionID, p.Description
FROM dbo.Users AS u
CROSS JOIN dbo.BasePermissions AS p
WHERE u.IsAdmin = 1;

This will yield a set of rows (number of admin users * number of permissions), with every permission listed for every admin user. It avoids you having to store every single set, and also means you don't have to update all admin users when you add or remove a permission.

(This is not a super practical example, because the app could be written in such a way that recognizes an admin user, but let's say the app can't be changed.)

As an aside, resist the temptation to ever use a RIGHT JOIN. Stick to LEFT, it's a much simpler way to think about relationships conceptually IMHO. Every time I see a RIGHT JOIN I re-write it to LEFT before I do anything else.

Answer 2

A CROSS JOIN gives you the Cartesian product of all the rows in the two tables and then applies any filtering from the WHERE clause. It rarely serves a purpose for your more common table relationships, but there are some occasions where it's handy.

For example, you may need to assign relationships between a static table of Properties and some Items stored in another table. The easiest way to add a new Item and relate it's needed Properties would be to insert the results of a CROSS JOIN between the two tables into a linking table between them. Specific values for those properties could then be added later via other interfaces, but they would have the framework assigned already without having to hard-code the interface.