What Are the Uses for Cross Join

What are the uses for Cross Join?

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.

Why and when to use CROSS JOIN instead of INNER JOIN with UPDATE statements?

There is no good reason that I can imagine to do this. The query is either written incorrectly, or just a test to slow down your system or to invalidate the target table's data (or perhaps, just to see what it does).

It will probably set COL1 of every row in Table1 to the same single random value from Table2's COL1 (though probably either the first or last such value). But it will do so very inefficiently (unless the optimizer in later versions of SQL Server have optimized out this useless case, I haven't tested it in years myself).

What is the difference between using a cross join and putting a comma between the two tables?

They return the same results because they are semantically identical. This:

select * 
  from A, B

...is (wince) ANSI-89 syntax. Without a WHERE clause to link the tables together, the result is a cartesian product. Which is exactly what alternative provides as well:

    select * 
      from A 
cross join B

...but the CROSS JOIN is ANSI-92 syntax.

About Performance

There's no performance difference between them.

Why Use ANSI-92?

The reason to use ANSI-92 syntax is for OUTER JOIN support (IE: LEFT, FULL, RIGHT)--ANSI-89 syntax doesn't have any, so many databases implemented their own (which doesn't port to any other databases). IE: Oracle's (+), SQL Server's =*

sql cross join - what use has anyone found for it?

One use I've come across a lot is splitting records out into several records, mainly for reporting purposes.

Imagine a string where each character represents some event during the corresponding hour.

ID | Hourly Event Data
1  | -----X-------X-------X--
2  | ---X-----X------X-------
3  | -----X---X--X-----------
4  | ----------------X--X-X--
5  | ---X--------X-------X---
6  | -------X-------X-----X--

Now you want a report which shows how many events happened at what day. Cross join the table with a table of IDs 1 to 24, then work your magic...

SELECT
   [hour].id,
   SUM(CASE WHEN SUBSTRING([data].string, [hour].id, 1) = 'X' THEN 1 ELSE 0 END)
FROM
   [data]
CROSS JOIN
   [hours]
GROUP BY
   [hours].id

=>

1,  0
2,  0
3,  0
4,  2
5,  0
6,  2
7,  0
8,  1
9,  0
10, 2
11, 0
12, 0
13, 2
14, 1
15, 0
16, 1
17, 2
18, 0
19, 0
20, 1
21, 1
22, 3
23, 0
24, 0

CROSS JOIN vs INNER JOIN in SQL

Cross join does not combine the rows, if you have 100 rows in each table with 1 to 1 match, you get 10.000 results, Innerjoin will only return 100 rows in the same situation.

These 2 examples will return the same result:

Cross join

select * from table1 cross join table2 where table1.id = table2.fk_id

Inner join

select * from table1 join table2 on table1.id = table2.fk_id

Use the last method

SQL Cartesian Products, Cross JOIN how are they useful?

One example:

Say that all pets should have a record for all events, how do you find which rows are missing?

Using a cross join gets you the cartesian product of pets/events, which is what the event table should contain if all pets had a record for all events. This you then can join with (or use set difference) to find the missing rows.

SQL Cross Join practical use

Sure. Say I want to write a report that has a list of every shape and color, and linked to that a number of people who say that it's their favorite shape and color

Shapes
------
Triangle
Square

Colors
-----
Red
Green
Blue

People
-----
John, Blue, Triangle
Mary, Blue, Triangle
Steve, Red, Square

SELECT 
  s.Shape,
  c.Color,
  Count(*)
FROM
  Shapes s 
  CROSS JOIN Colors c 
  LEFT JOIN People p ON s.Shape = p.Shape and c.Color = p.Color
GROUP BY
  s.Shape,
  c.Color

The results have every shape and color combo even though my people data doesn't

Red,   Triangle, 0
Green, Triangle, 0
Blue,  Triangle, 2
Red,   Square,   1
Green, Square,   0
Blue,  Square,   0

We can use cross join when we want to create combinations, especially if we don't have those combinations anywhere in our actual data

SQL Server: What is the difference between CROSS JOIN and FULL OUTER JOIN?

A cross join produces a cartesian product between the two tables, returning all possible combinations of all rows. It has no on clause because you're just joining everything to everything.

A full outer join is a combination of a left outer and right outer join. It returns all rows in both tables that match the query's where clause, and in cases where the on condition can't be satisfied for those rows it puts null values in for the unpopulated fields.

This wikipedia article explains the various types of joins with examples of output given a sample set of tables.

---

Related Topics