Can you CROSS JOIN on more than two tables?


#1

Question

In the context of this exercise, can we perform CROSS JOIN on more than two tables?

Answer

Yes, you can CROSS JOIN as many tables as you want.

Let’s build upon the example from the exercise, which had the tables shirts and pants, and add a third table for socks.

We can perform the CROSS JOIN for all three tables, like so

SELECT shirts.shirt_color,
pants.pants_color,
socks.sock_color
FROM shirts
CROSS JOIN pants
CROSS JOIN socks;

If the tables had 3 shirts, 2 pants, and 6 socks, then the result of this CROSS JOIN will give

3 x 2 x 6 = 36 combinations, or 36 total rows.

One thing to note is that when you use CROSS JOIN without a WHERE clause, like the example above, we get every single combination of the table rows.

As a result, this can quickly grow larger as you CROSS JOIN more tables because the growth is multiplicative.