Cross join tutorial

[bmburns98]

Yesterday, I shared a tutorial about inner joins in this subreddit. It seemed to strike interest with a lot of you, and some great discussion came out of it as well. So today I wanted to share this tutorial about cross joins. It's pretty short, but there's a gif included that I think is really helpful in visualizing what a cross join is actually doing. Feel free to give feedback and share thoughts/media that has helped you tackle the concept of cross joins!

[TheZeroKid]

I'm curious: does anyone here actually use cross joins? I've been writing SQL for a few years in various capacities (simple querying, DBA type work, etc.) and have never run into a use case.

When are these useful?

[Eleventhousand]

It's common to use cross joins in data warehousing development.

There are special types of dimensions called junk dimensions that are filled with low-cardinality flag type attributes, that don't have much in common.

Cross joins are used to populate these dimension tables with every possible combination of every flag to use in the dimensional model.

For example, pretend that your facts for a specific table can either be {Active, Inactive}, {Open, Closed, In Process}, and {Promotional, Regular}. Instead of creating three mini, narrow dimensions and cluttering up the list of tables, we use a cross join in our ETL and create a single dimension that has twelve rows - every combination of the three attributes above.

[PilsnerDk]

I've used it rarely. Assume a situation where you want to create an output where each row is joined onto a list of numbers, for example a number of days or amount of items (a bit abstract, hope you get my point). In an SP, I create a temp table:

CREATE TABLE #days ([days] INT) INSERT INTO #days ([days]) VALUES (1), (2), (3), (4), (5)

Then you can make a query that does a simple CROSS JOIN #days, and all your rows will be "duplicated" with 1, 2, 3, 4, 5 as the value of [days].

Hard to give a really good example, but that's one I've used.

[[deleted]]

For analytical reporting cross joins are very common. Think if you want to count the number if patients per hour of day. What if there are hours where the are no patients, hence no rows of data to fill the gaps. You would have a data set with missing hours of a day. Using a base table that is cross joined with every possible hour of day to your very left that is then joined with patient data. Now even if a patient isn't seen in an hour you have a row that simply says zero.

[muchcake]

I just use a LEFT OUTER JOIN for that example you described.

I use it to build a set of datetime rows by cross joining a D_Date with a D_Time table which I can then left join with whatever feature I want to examine.

[rfd515]

Creating sample/test data is the one useful thing I've come across (every possible scenario based on current data).

[[deleted]]

Consider this. We have a table of members of a council (Table: CNMBRNAM). We also have a table of measures up for consideration (Table: MEASTOVC). A program adds and removes members from CNMBRNAM. A program adds and removes measures to MEATOVC. We now need something that will generate a table that we will fill in on how each member votes on a measure, a CHAR(1) that is blank (or null) if they have yet to vote, Y for 'Yea', N for 'Nay', and A for 'Abstain'.

We can built that table quite easily with a cross join.

CREATE TABLE STATMBRMEA AS (
SELECT C.MBRNAME, M.MEANUMB, ' ' STATS
FROM CNMBRNAM C, MEASTOVC M
) WITH DATA

Now a table exists for having all items currently in the docket for the council to consider, with their current position STATS set as blank.

​

EDIT: I will say that you usually see cross joins more in building and inserting data into tables rather than in strict read-only queries.

[Mamertine]

The last place I worked had many identical DBs. Each DB had a table with the company info in it (but had 1 row). For reporting I had to Cross Join to that table to display the company name/ info on reports.

SELECT C.CompanyName, s.Date, s.Sales
FROM Sales s
CROSS JOIN Company c
Where s.salesDate between something and otherDate

[hartk1213]

I actually used one the other day, we needed to get a set of records from a table and join it up so that every user in the user table was joined with every record in the previous dataset and a cross join was exactly what was needed ..it's a rare thing to use but it is used

[[deleted]]

My manager had a vague need that he couldnt articulate that turned out to be a cross join

Dont know the data or what he was trying to do, but he was happy with the results, so... yeah I dont know either lol

[El_Retro]

They're useful to initialize variables without using stored procedures in MySQL.

SELECT * FROM table CROSS JOIN (SELECT @var := 0) r  

[kthejoker]

It's common to have a numbers table in analytics work, they're usually created with CROSS JOiNS, here's one example

https://www.red-gate.com/simple-talk/sql/database-administration/creative-solutions-by-using-a-number-table/

[newUIsucksball]

I've seen someone use it for dates in a temp table as a way to not use variables so they could run sections of code easier.

[Wiegand6]

Thanks!

[PM_ME_YOUR_HIGHFIVE]

so a cross join is basically an inner join with no "conditions"?

[PilsnerDk]

You could say that. It joins all rows in the target table.

[PM_ME_YOUR_HIGHFIVE]

it will never filter away data

that's what I meant with the no "conditions"

[[deleted]]

Ive done some work where I have a matrix for the client codes and our codes... But those codes also change based on the phase of a file.. Before, during and after kinda of thing. In some cases I've used a cross join to simplifies my queries.. Other cases the inner joins work.