What are the benefits of using Cross Join?

Question

Cross join performs a Cartesian product on tuples of two sets.

SELECT * FROM Table1 CROSS JOIN Table2 

What circumstances make such an SQL operation particularly useful?

Answer 1

If you have a “grid” that you want to completely fill out, for example, the size and color of information for a particular piece of clothing:

 select size, color from sizes CROSS JOIN colors 

Perhaps you need a table containing a row for every minute of the day, and you want to use it to verify that the procedure was running every minute, so you can cross three tables:

 select hour, minute from hours CROSS JOIN minutes 

Or you have a set of standard report specifications that you want to apply to each month of the year:

 select specId, month from reports CROSS JOIN months 

The problem with preserving these ideas is that in most cases you do not need a complete product, especially with regard to clothing. You can add MINUS logic to the query to remove certain combinations that you don’t wear, but it may be easier for you to fill out the table in some other way than to use the Cartesian product.

Alternatively, you can try cross-joining tables that have perhaps more rows than you thought, or maybe your WHERE was partially or completely missing. In this case, your database administrator will immediately notify you of the omission. Usually he or she will not be happy.

Answer 2

Usually you do not need a complete Cartesian product for most database queries. The whole strength of relational databases is that you can apply any restrictions that may interest you so that you cannot pull unnecessary rows from db.

I believe that one far-fetched example is where you might want it to be if you have a table of employees and a table of tasks that you need to complete, and you want to see all the possible assignments of one employee to one task.

Answer 3

Well, that probably won't answer the question, but if it's true (and I'm not even sure about it), this is a funny story.

In the early days of Oracle, one of the developers realized that he needed to duplicate each row in the table (for example, maybe it was an event table, and he needed to change it separately to “start event” and “end event”). records). He realized that if he had a table with two rows, he could cross-connect by selecting only the columns in the first table and get exactly what he needs. So he created a simple table, which he naturally called "DOUBLE."

Later, he needs to do something that can only be done by selecting from the table, even if the action itself has nothing to do with the table (maybe he forgot his watch and wanted to read the time using SELECT SYSDATE FROM ...) He understood that he still had his DOUBLE table, and used it. After a while, he was tired of seeing time printed twice, so he eventually deleted one of the rows.

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

This explains why a table, the only meaning of which is that it has one row, has a name that means two.

Answer 4

Generate data for testing.

Answer 5

The key is "show me all the possible combinations." I used them in combination with other computed fields, and then sorted / filtered them.

For example, let's say you are building an arbitrage (trading) application. You have sellers offering products at a price, and buyers ask for products at a price. You make a cross on the product key (to match potential buyers and sellers), calculate the spread between price and price, then sort desc. on this, in order to give you (the intermediary) the most profitable transactions for execution. Of course, you will always have other restriction filter criteria.

Answer 6

Does something like a numbers table, which has ten rows for numbers 0-9. You can use the cross join in this table several times to get a result that, however, has many rows that you need, and the results are numbered accordingly. This has a number of uses. For example, you can combine it with the datadd () function to get a set for each day in a particular year.

Answer 7

This is an interesting way to use cross-join to create a crosstab report . I found it in Joe Celko SQL for Smarties and used it several times. It took a little tweaking, but was worth the time.

Answer 8

Imagine that you had a series of requests that you want to publish for a certain combination of elements and dates (prices, availability, etc.). You can load items and dates into separate temporary tables, and your queries intersect with tables. This may be more convenient than an alternative to listing elements and dates in IN clauses, especially since some databases limit the number of elements in an IN clause.