Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What are the uses for Cross Join?

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?

like image 560
Llyle Avatar asked Oct 20 '08 20:10

Llyle


People also ask

What is the purpose of cross join in SQL?

In SQL, the CROSS JOIN is used to combine each row of the first table with each row of the second table. It is also known as the Cartesian join since it returns the Cartesian product of the sets of rows from the joined tables.

What is the use of cross join in Oracle?

A CROSS JOIN is a JOIN operation that produces the Cartesian product of two tables. Unlike other JOIN operators, it does not let you specify a join clause. You may, however, specify a WHERE clause in the SELECT statement.

What does a cross join or no join accomplish?

Unlike other joins, a cross join uses no join conditions. It produces a combination of all rows from the tables joined. In other words, it produces a cartesian product of the two tables.

What is the need of self join and cross join?

Inner join or Left join is used for self join to avoid errors. 2. Cross Join : Cross join allows us to join each and every row of both the tables.


4 Answers

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.

like image 191
Dave DuPlantis Avatar answered Oct 19 '22 15:10

Dave DuPlantis


Generate data for testing.

like image 27
Ovidiu Pacurar Avatar answered Oct 19 '22 15:10

Ovidiu Pacurar


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.

like image 15
Randy Avatar answered Oct 19 '22 16:10

Randy


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.

like image 14
Kevin Dostalek Avatar answered Oct 19 '22 17:10

Kevin Dostalek