What are the uses for Cross Join?

Question

A cross join performs a cartesian product on the tuples of the two sets.

SELECT *
FROM Table1
CROSS JOIN Table2

Which circumstances render such an SQL operation particularly useful?

Answer 1

If you have a "grid" that you want to populate completely, like size and color information for a particular article of clothing:

select 
    size,
    color
from
    sizes CROSS JOIN colors

Maybe you want a table that contains a row for every minute in the day, and you want to use it to verify that a procedure has executed each minute, so you might cross three tables:

select
    hour,
    minute
from
    hours CROSS JOIN minutes

Or you have a set of standard report specs that you want to apply to every month in the year:

select
    specId,
    month
from
    reports CROSS JOIN months

The problem with maintaining these as views is that in most cases, you don't want a complete product, particularly with respect to clothes. You can add MINUS logic to the query to remove certain combinations that you don't carry, but you might find it easier to populate a table some other way and not use a Cartesian product.

Also, you might end up trying the cross join on tables that have perhaps a few more rows than you thought, or perhaps your WHERE clause was partially or completely missing. In that case, your DBA will notify you promptly of the omission. Usually he or she will not be happy.

Answer 2

Generate data for testing.

Answer 3

You're typically not going to want a full Cartesian product for most database queries. The whole power of relational databases is that you can apply whatever restrictions you might be interested in to allow you to avoid pulling unnecessary rows from the db.

I suppose one contrived example where you might want that is if you have a table of employees and a table of jobs that need doing and want to see all possible assignments of one employee to one job.

Answer 4

Ok, this probably won't answer the question, but, if it's true (and I'm not even sure of that) it's a fun bit of history.

In the early days of Oracle, one of the developers realized that he needed to duplicate every row in a table (for example, it's possible it was a table of events and he needed to change it separate "start event" and "end event" entries). He realized that if he had a table with just two rows, he could do a cross join, selecting just the columns in the first table, and get exactly had he needed. So he created a simple table, which he naturally enough called "DUAL".

Later, he need to do something which could only be done via a select from a table, even though the action itself had nothing to do with the table, (perhaps he forgot his watch and wanted to read the time via SELECT SYSDATE FROM...) He realized that he still had his DUAL table lying around, and used that. After a while, he tired of seeing the time printed twice, so he eventual deleted one of the rows.

Others at Oracle started using his table, and eventually, it was decided to include it in the standard Oracle installation.

Which explains why a table whose only significance is that it has one row has a name which means "two".

Answer 5

The key is "show me all possible combinations". I've used these in conjunction with other calculated fields an then sorted/filtered those.

For example, say you are building an arbitrage (trading) application. You have sellers offering products at a price and buyers asking for products at a cost. You do a cross join on the product key (to match up the potential buyers and sellers), calculate the spread between cost and price, then sort desc. on this to give you (the middleman) the most profitable trades to execute. Almost always you'll have other bounding filter criteria of course.

Answer 6

Takes something like a digits table, which has ten rows for the digits 0-9. You can use cross join on that table a few times to a get a result with however many rows you need, and each row will be numbered appropriately. This has a number of uses. For example, you can combine it with a dateadd() function to get a set for every day in a given year.

---

Note: this post is old now. Today I'd use generate_series() or a recursive CTE to do this job instead.

Answer 7

This is an interesting way to use a cross join to create a crosstab report. I found it in Joe Celko's SQL For Smarties, and have used it several times. It does take a little setup, but has been worth the time invested.

Answer 8

you can use it CROSS JOIN to:

• generate data for testing purposes
• combine all properties - you need all possible combination of e.g blood groups (A,B,..) with Rh-/+, etc... --tune it for your purposes;) - I'm not expert in this area;)

CREATE TABLE BL_GRP_01 (GR_1 text);
CREATE TABLE RH_VAL_01 (RH_VAL text);
INSERT INTO BL_GRP_01 VALUES ('A'), ('B'), ('AB'), ('O');
INSERT INTO RH_VAL_01 VALUES ('+'), ('-');

SELECT CONCAT(x.GR_1, y.RH_val)
       FROM BL_GRP_01 x
 CROSS JOIN RH_VAL_01 y
ORDER BY CONCAT(x.GR_1, y.RH_VAL);

• create a join for 2 tables without a common id and then group it using max(),etc.. to find highest possible combination

Answer 9

Imagine you had a series of queries you want to issue over a specific combination of items and dates (prices, availability, etc..). You could load the items and dates into separate temp tables and have your queries cross join the tables. This may be more convenient than the alternative of enumerating the items and dates in IN clauses, especially since some databases limit the number of elements in an IN clause.