Code Editor

Query Results

Run a query to see results.

Database Schema

Schema undefined.
Multiple Tables

Cross Join

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 that described different shirts we own, and another table called pants that described different pants that we owned, we might want to know all possible combinations of shirts and pants to create outfits.

Our code might look like this:

SELECT shirts.shirt_color, pants.pant_color FROM shirts CROSS JOIN pants;
  • The first two lines select the columns shirt_color and pant_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 three different shirts (red, yellow, and blue) and two different pants (navy and black), the results might look like this:

shirt_color pant_color
red navy
red black
yellow navy
yellow black
blue navy
blue black

This clothing example is fun, but it's not very practically useful. A more common usage of CROSS JOIN 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 two columns that we haven't discussed yet:

  • 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 (1, 2, 3) we would need to know if a user was subscribed. Follow the steps below to see how we can use a CROSS JOIN to solve this problem.

Report a Bug
If you see a bug or any other issue with this page, please report it here.