Cross Join

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

BLOG_0032 - PIC01 - TBL_A

TBL_B

BLOG_0032 - PIC02 - 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:

BLOG_0032 - PIC03 - Example 1

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:

BLOG_0032 - PIC04 - TBL_VALUE_WEEK

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:

BLOG_0032 - PIC05 - Example 2

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)!

Download

BLOG_0032_Cross_Join_Testdate_Examples.zip

Share and Enjoy: Diese Icons verlinken auf Bookmark Dienste bei denen Nutzer neue Inhalte finden und mit anderen teilen können.
  • LinkedIn
  • XING
  • Facebook
  • Google Bookmarks
  • Twitter

Hinterlasse eine Antwort

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind markiert *