Cross Join

[oldoc63]

So far, we've focused on matching rows that have some information in common. Sometimes, we just want to combine all rows of one table with all rows of another table. For instance, if we had a table of shirts and a table of pants, we might want to know all the possible combinations to create different outfits. Our code might look like this:

[oldoc63]

• The first two lines select the columns shirt_color and pants_color.
• The third line pulls data from the table shirts.
• The fourth line performs a CROSS JOIN with pants.

Notice that cross joins don't require an ON statement. You're not really joining on any columns.

If we have 3 different shirts (white, grey and olive) and 2 different pants (light denim and black), the results might look like 3 shirts x pants = 6 combinations!

A more common usage of CROSS JOIN is when we need to compare each row of a table to a list of values.

Let's return to our newspaper subscriptions. This table contains the columns:

• start_month: the first month where the customer subscribed to the print newspaper (i.e., 2 for February)
• end_month: the final month where the customer subscribed to the print newspaper

Suppose we wanted to know how many users were subscribed during each month of the year. For each month we would need to know if a user was subscribed.

[oldoc63]

Instructions

1. Let's start by counting the number of customers who were subscribed to the newspaper during March.
   • Use COUNT(*) to count the number of rows and a WHERE clause to restrict to two conditions:
   • start_month <= 3
   • end_month >= 3

[oldoc63]

2. The previous query lets us investigate one month at a time. In order to check across all months, we're going to need to use a cross join. Our database contains another table called months (1-12). Select all columns from the cross join of newspaper and months.

[oldoc63]

3. Create a third query where you add a WHERE statement to your cross join to restrict to two conditions:
   • start_month <= month
   • end_month >= month

This will select all months where a user was subscribed.

[oldoc63]

4. Create a final query where you aggregate over each month to count the number of subscribers.