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.