CROSS JOIN

This is a less commonly used join type, but still relevant. The CROSS JOIN joins every row in the left table with every row in the right table.

Suppose you have 2 tables representing types of pizza toppings, 1 for meats and 1 for cheeses.

There are 2 meats available to choose from, and 3 cheeses. Suppose you can choose 1 cheese and 1 meat for your pizza. That means there are 2 · 3 = 6 possible pizza combinations to make. If you want to list all possible combinations, you can CROSS JOIN.

SELECT *
FROM meats
CROSS JOIN cheeses;

Observe that every meat is joined with every cheese. In relational algebra, this type of operation is called a cartesian product. That is when you put 2 sets together to form a new set of pairs. The members of the new set are every possible pair of 1 thing from each set. I find it easier to just visualize it.

As with other joins, it generalizes to n number of joins. If you got to choose 1 meat, 2 cheeses, and a veggie, you could do something like this:

SELECT *
FROM meats
CROSS JOIN cheeses
CROSS JOIN cheeses
CROSS JOIN veggies;

However, the number of combinations grows extremely quickly.

This operation is actually quite uncommon in everyday development. Most real tables are quite large, and actual CROSS JOINs with them would be outrageously large…

…unless you limit the rows with a WHERE clause. We'll discuss that in the next section.

A CROSS JOIN Is an Inner Join

This same operation could've been done with a join type you've learned already.

SELECT *
FROM meats
JOIN cheeses
  ON TRUE;

A CROSS JOIN is just an inner join without an ON clause, and we can recapitulate its behavior using JOIN by setting the on clause to ON TRUE, i.e. always match.

So if we can make an INNER JOIN act like a CROSS JOIN, can we make a CROSS JOIN act like an INNER JOIN? Yes we can. Remember this example from our app Tweeter?

SELECT
  tweeters.email,
  tweets.body,
  tweets.created_at
FROM tweeters
JOIN tweets
  ON tweeters.id = tweets.user_id
WHERE tweeters.email = 'judy14@yahoo.com';

You can write this as a CROSS JOIN like this:

SELECT
  tweeters.email,
  tweets.body,
  tweets.created_at
FROM tweeters
CROSS JOIN tweets
WHERE tweeters.id = tweets.user_id
  AND tweeters.email = 'judy14@yahoo.com';

Basically, to convert an inner join to a cross join, you can just move the ON clause condition into the WHERE clause. A WHERE clause makes the CROSS JOIN's number of rows reasonable.

It turns out that the inner join is just syntactic sugar for the cross join. In the real world, people tend to prefer to write their joins as inner joins, as the ON clause nicely separates the condition relevant for joining from the rest of the WHERE conditions.

When to Use CROSS JOIN

Since the INNER JOIN is a nicer way to write joins when there is some join condition, it only makes sense to prefer the CROSS JOIN when there is no join condition. As we've seen, the situations where you want a CROSS JOIN without any join condition are limited. However, there are some cases where it makes sense.

1. The left or right table has only 1 row.

We saw an example of this in the lesson about common table expressions. One of the CTEs had a 1-row table.

Though the official solution doesn't have it, many people would use a CROSS JOIN in the challenge Test Scores.

2. To unnest an array.

If you have a table with an array column, unnesting the array elements into separate rows and joining them with the row the original array was in is common when working with arrays.

More Syntactic Sugar

It turns out there's another equivalent way to write CROSS JOIN. You can write it as a comma-delimited list in the FROM clause.

SELECT *
FROM meats, cheeses;

Whether you write it this way or with the CROSS JOIN keywords is up to personal preference.