Can you CROSS JOIN on more than two tables?

Frequently Asked Questions SQL FAQ
1

Question

In the context of this exercise, can we perform CROSS JOIN on more than two tables?

Answer

Yes, you can CROSS JOIN as many tables as you want.

Let’s build upon the example from the exercise, which had the tables shirts and pants, and add a third table for socks.

We can perform the CROSS JOIN for all three tables, like so

SELECT shirts.shirt_color,
pants.pants_color,
socks.sock_color
FROM shirts
CROSS JOIN pants
CROSS JOIN socks;

If the tables had 3 shirts, 2 pants, and 6 socks, then the result of this CROSS JOIN will give

3 x 2 x 6 = 36 combinations, or 36 total rows.

One thing to note is that when you use CROSS JOIN without a WHERE clause, like the example above, we get every single combination of the table rows.

As a result, this can quickly grow larger as you CROSS JOIN more tables because the growth is multiplicative.

11 Likes
2

I’ve got a question on the code in this exercise. I can’t wrap my head around this.

SELECT month,
   COUNT(*) AS 'subscribers'
FROM newspaper
CROSS JOIN months
WHERE start_month <= month 
   AND end_month >= month
GROUP BY month;

In the above WHERE statement, I don’t understand
WHERE start_month <=month
AND end_month >=month

After the greater than or less than, why is the argument month? What is that doing?

28 Likes
3

WHERE start_month <=month
AND end_month >=month

After the greater than or less than, why is the argument month? What is that doing?

This WHERE clause compares all the values in ‘start_month’ and ‘end_month’ columns from newspaper table with the values in ‘month’ column from months table.

It is used to select only the rows with the months when the person was actually subscribed.
The rows inside the red rectangle will not be selected in this case.

untitled

38 Likes
4

essentially this is doing the same thing as (end_month - start_month) +1?

2 Likes
5

yes, it is but i dont think we can do math in SQL queries

1 Like
6

Actually i think and i stand to be corrected but the CROSS JOIN function should be accompanied by a WHERE clause in order to give a more meaningful result.

2 Likes
7

I understand this, but what query would I use if I wanted to determine the total number of items? Meaning, what clause could single out each clothing item in a joint –or cross joint– table in order to get the total amount of distinct rows, as in 3 + 2 + 6 = 11? I’ve tried DISTINCT to not much avail.

8

NO, it’s qualifying the current month it is querying.

If the current month in the process (month) is
(greater than the start month
AND
lesser than the end month, )
THEN
the current month being compared
is WHILE the subscription is active.

1 Like
9

Hi ,
I have tied the same. but the code throwing error.

CROSS JOIN Code was Not working
CROSS JOIN Code was Not working1405×579 22.5 KB

Can Anyone help me on this?

10

Hi, check for typo on CROSS (your screenshot shows CORSS instead).

7 Likes
11

Hi, did you mean "…the total amount of distinct rows, as in 1 * 2 * 6 = 12" to count all matches for one particular color of shirt?
Or try to describe your issue a little bit further, maybe with some code example with unknown for you parts.

Because now i’m prone to think that you should use COUNT clause or GROUP BY, but can’t help more due to lack of information.

12

You would not be using CROSS JOIN, but rather UNION function. This means that when you add all the entries from the second table into the first, they will be added as if separate entries. From there you can use aggregate it using COUNT().

CROSS JOIN creates combination of all the entries in the second table with the first table.

2 Likes
13

Can someone walk through the logic here in plane english. I actually get the bottom part but am struggling to understand the first 3 lines given Month is a column in Months (not newspaper) but its Selecting FROM newspaper?

SELECT month,
COUNT(*) AS ‘subscribers’
FROM newspaper
CROSS JOIN months
WHERE start_month <= month
AND end_month >= month
GROUP BY month;

14

Since you CROSS JOIN months, your query will have access to that ‘month’ column as well. The way I understand it, it then selects all of that from your new crossed-joined table that contains everything.

2 Likes
15

From the point of process steps select is merely command to display results of computation, so literally joins are executed first providing a table results to memory from which you will select necessary fields, so select comes the last logical step in the process…more like a print command

16

Hi, can anyone let me know that after this query, how can I use MAX to find the month with the maximum subscription?

image
image758×644 44.5 KB

I cannot get any result by applying “MAX(COUNT())", do I need to create another table to host the previous query result, then apply "MAX(COUNT())” on this newly created table?

Appreciate your response, thanks!

17

I think this reply might be useful to you-

18

U can use with something like below:

[sql]
with test As (select month,count(*) as Subscriber from newspaper cross join months where start_month<=month and end_month>=month

group by month)

select month, max(Subscriber) from test;
[/sql]

19

didn’t use MAX() but achieves the desired result.

SELECT month, COUNT(*)
FROM newspaper
CROSS JOIN months
WHERE start_month <= month and end_month >= month
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;

2 Likes
20

@moonmullins The whole aim of this code is to count how many people had ACTIVE subscriptions during each month of the year. month is a number 1,2,3…,12 from the months table. The WHERE clause helps to filter the results to get those ACTIVELY subscribed in each month.

If we wanted to know how many new subscribers were added each month, we’d write
WHERE start_month = month

2 Likes