Hi,
today’s article is about Cross Joins and the few cases where you can use it.
In general a cross join just takes all rows from table A and combines them with all rows from table B. Other terms for cross join are cross product and cartesian product. Let’s make a small example. We need two tables:
TBL_A
TBL_B
Now you can do the CROSS JOIN with the following SQL:
| SELECT * FROM TBL_A CROSS JOIN TBL_B |
Compared with an inner join you write CROSS JOIN and you leave the ON clause. That one is not needed as you don’t define any joining condition. That is because all rows from A are taken and combined with all rows of B. The result looks like this:
As you can see the row one from table A (1 / A) is taken and combined with all three rows from table B. The result are three rows where the first two columns (that come from table A) are the same and the last two columns (that come from table b) are changing. The same is repeated with row two from table A (2 / A).
So you can calculate the number of resulting rows with rows in table A * rows in table B. In our example it’s 2*3 = 6 rows.
This type of join is seldom used as it makes sense only in a few cases. For example if you have a value per week and you want to distribute it on each day of the week. I have another table for that:
Now we run the following query:
| SELECT WEEK_START_DATE+DAY_OF_WEEK AS DAY_DATE, WEEK_VALUE FROM TBL_VALUE_WEEK CROSS JOIN ( SELECT LEVEL-1 AS DAY_OF_WEEK FROM dual CONNECT BY LEVEL<=7 ) week_days ORDER BY DAY_DATE |
And the result looks like this:
As you can see we have all seven days of the week starting with 2017-12-04 and each day has the WEEK_VALUE of this week. Then comes the next week.
Let’s take a closer look at the query. We have a subquery which is needed for generating the seven days of a week. This is database specific. In this case it is an opportunity on an Oracle database. You could also create a table with the numbers 0 to 7 and work with that instead of a subquery. If you are working on other databases you have to change that part.
Besides the subquery we are just accessing the weekly table cross joining it so in a result we get seven rows a week instead of one.
You find the example code for this article in the download section.
If you are interested in further information on SQL just check out my BLOG or take a look at my new SQL book. I’ve added a free excerpt on this BLOG as well. You can also attend training on my corporate homepage. At the moment my classroom trainings are only available in German. Inhouse training and online courses are also available in English language. So check out my trainings (German) or my online courses (English)!
