FAQ: Multiple Tables - Cross Join


#1

This community-built FAQ covers the “Cross Join” exercise from the lesson “Multiple Tables”.

Paths and Courses
This exercise can be found in the following Codecademy content:

Web Development
Data Science

Learn SQL

FAQs on the exercise Cross Join

Join the Discussion. Help a fellow learner on their journey.

Ask or answer a question about this exercise by clicking reply (reply) below!

Agree with a comment or answer? Like (like) to up-vote the contribution!

Need broader help or resources? Head here.

Looking for motivation to keep learning? Join our wider discussions.

Learn more about how to use this guide.

Found a bug? Report it!

Have a question about your account or billing? Reach out to our customer support team!

None of the above? Find out where to ask other questions here!


#2

Hi everyone!

So, about the CROSS JOIN almost everything is clear, only I don’t get totally where will I need to use it. But it’s like a sub-question, give answer on it with the main question if you can.

The main question is about instruction #3. Here we need to create a cross join query with a where statemant with two conditions:

start_month <= month AND end_month >= month;

(please look at the excersice to get wider understanding of what I’m talking about)

At the end of the instruction it says: “This will select all months where a user was subscribed.” The query I wrote, by this instruction, works perfectly, just I can’t undesrtand the logic behind it. Please, kindly explain how does it select’s all months where a user was subscribed?


#3

Hi Norlandius,

I wanted to answer your main question. I’m guessing your SQL query looks something like this:

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

Line 1 is basically saying pull the column called month
Line 2 is saying, reference the [cross joined newspaper table +months table] to pull the month and count the number of rows for each month value (this value ranges from 1-12)

Line 3 adds a condition, so that the query can work for every value that’s listed in the month column from Line 1 (where the values range from 1-12) imagine this simpler query listed below is being run 12 times in the background once for each month. That’s why the above query is important and powerful.

SELECT COUNT(*)
FROM newspaper
WHERE start_month= 1
AND end_month = 1;

This exercise is useful because you can now analyze your subscription trend…is it growing, declining, at what rate etc.

Hope that helps!


#4

Just adding to muqaddasibrahim’s answer.

What CROSS JOIN does is the following:
–> For each line of newspaper it will add all the lines from months.
–> This way, line 1 from table newspaper will appear 12 times now, 1 per each month. The same applies to all the other lines

–> the logic of the WHERE condition will filter out lines whose months are outside the range
start_month< month < end_month

This way you can count how many lines there are. If grouped by months, it will show how many lines there are for that specific month, which is the number of subscriptions.


#5

Hi there!
I didn’t quite understand, why we use start_month <= 3
end_month >= 3 conditions instead of just ‘start month = 3’ in instruction #1. Can someone explain it to me please? Appreciate it


#6

This gets explained in the Hint…

“During March” means that the customer’s starting month was in or before March and final month was in or after March:


#7

Using start_month = 3 will only include subscriptions starting on March but not the ones that started before and end on March or later.

To be subscribed during March the subscription must begin in March (or before) and end on March (or after). So, the condition requires start_month <= 3 AND end_month >= 3 (where 3 is March). The following subscriptions includes March.

  • From 1 to 3 start_month = 1 AND end_month = 3
  • From 1 to 5 start_month = 1 AND end_month = 5
  • From 3 to 5 start_month = 3 AND end_month = 3

The following subscriptions doesn’t include March

  • From 1 to 2 (because end_month >= 3 is false)
  • From 4 to 5 (because start_month <= 3 is false)

So, both conditions established in the hint must be true at the same time and that requires the use of the AND clause.


#8

I also struggled with this. I understand how the WHERE works and how the cross join brings up all the possibilities and then you filter out those that did not fit.
It is a very elegant method, but it is the first one where I have wondered how I would ever find another context for it and remember how to do it. Maybe that will come with later projects. I trust the trainers and I am really enjoying the course.


#9

Can someone help me? I’m either really missing something or the site isn’t working for me. I copied the exact cross join they had in Lesson 3 (see image). And yet the code isn’t working for me. Can someone help me out or tell me if I’m missing the obvious?


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

I get the code on lines 5 onward. But why do I select month, but then COUNT(*)? Why not COUNT(month)? Why is it FROM newspaper? newspaper doesn’t have a month column, so surely it would be FROM month? And so, I CROSS JOIN newspaper?


#11

same issue here. I don’t know why the website keep asking me “did you aggregate by month” even though I copy exactly the same code from Hint. This pull me back for weeks andI can’t move on to next step, burning my money and time.


#12

By reading the Cross-Join exercise and the examples , I was wondering , why in example 3 & 4, we do not simply use INNER JOIN instead of CROSS JOIN? In the Inner Join exercise it was written “When we perform a simple/inner join , the result only includes rows that match on our condition”.
By following that definition, It seems to me that INNER JOIN can be applied at the above examples since we restrict the query by writing start_month <= month & end_month >= month. which helps us make a condition match.
I actually used the INNER JOIN for example No. 3 and I wrote the code as it follows :

SELECT *
FROM newspaper
INNER JOIN months
ON start_month <= month
AND end_month >= month;

& for example No.4:

SELECT month,
COUNT(*)
FROM newspaper
INNER JOIN months
ON start_month <= month
AND end_month >= month
GROUP BY month;

There was not any error message about the code. The solutions were correct and exactly the same as those of codeacademy’s instructions which used the CROSS JOIN!

I would appreciate if someone helped me with my confusion with INNER & CROSS JOIN concepts. Thanks.