Question
In the context of this exercise, what does a CROSS JOIN
do?
Answer
The CROSS JOIN
function returns a Cartesian product of all the rows from the first table, with all rows of the second table. As a result, if there are M
rows in the first table, and N
rows in the second table, then there will be a total of M x N
rows in the result. Each row from the first table will be combined with each row from the second table.
For example, if the tables were as follows,
table 1
x1
x2
x3
table 2
y1
y2
y3
Then the result of applying CROSS JOIN
to these would be
x1 y1
x1 y2
x1 y3
x2 y1
x2 y2
x2 y3
x3 y1
x3 y2
x3 y3
6 Likes
In code challenge 6 of working with multiple tables, I am unable to understand why CROSS JOIN is used to cross join premium_users and months table. Can someone please explain?
Thanks
7 Likes
You might have figured it out after moving on to the next challenge but I figure I would mention this just in case.
The cross join creates a duplicate record of the specific user_id, purchase_date, cancel_date for each month. So if there are 4 months in the ‘months’ table then you would have 4 records with the same user_id and purchase/cancel dates while having a unique ‘month’ value.
In the next challenge they use this data to then compare those months to the purchase/cancel dates to determine if the user was ‘active’ during each month.
9 Likes