Examples of when to use a cross join?

Question

In SQL can anyone give me some good examples of when you would use a cross join?

Duplicate Questions

• What are the uses for Cross Join?
• sql cross join - what use has anyone found for it?

Answer 1

Option 1: it can be used for easlity generation a big amount of data (for example for testing purposes).

Answer 2

Actually, I'm trying to figure out the best way to use a CROSS JOIN in MS Access 2003 as we speak.

Basically, We have a database with Customers, Service Plans (yearly plans detailing the amounts of money allocated to each of several service allocations), Allocations (specific pots of money within a service plan), Service Codes (each allocation has a service code), Expense Records, Support Providers.

I'm trying to figure out how many monthly services each of our providers has been paid for in the last 6 mos, including those who've been paid NOTHING (0 expenses) in the last 6 mos. But regular join queries only give COUNTs of ACTUAL expenses, but don't give zeroes for service codes in which no expenses were logged. Thus I want to get the cross-product of all Support Providers with all Service Codes, such that each provider has one record for each service code possible in the database and thus each service will either have a Total or a 0 in it.

Hope that helps.

Still trying to figure out the best way to do that in Access, which seem to not have the best support for such things. Just not quite sure on the syntax when combining with other tables using regular joins...

Answer 3

I've used exactly one in my career in anger, as a technique for rapidly matching many-to-many relationships. The problem was being able to search out relationship between two classes of groups from either side.

There's any number of ways to solve that programatically but since we had billions of possibilities it turned out matching arrays of simple string keys with a cross join was orders of magnitude faster than anything else.