Skip to main content

Cross Joins

Premium

A CROSS JOIN is a type of join in SQL that combines every row from one table with every row from another table, creating a Cartesian product of the two tables. The result set includes all possible combinations of rows from both tables.

Basic Syntax

Here's the basic syntax for a CROSS JOIN:

SQL
SELECT column_name(s)
FROM table1
CROSS JOIN table2;

Note that, unlike other join types we've learned about, the cross join syntax does not make use of the ON condition since it always combines all rows. In fact, a cross join is equivalent to creating an inner join where the join condition always evaluates to true. For example:

SQL
SELECT column_name(s)
FROM table1
JOIN table2
ON true;

When to use a cross join

CROSS JOIN is a powerful join type, but it should be used with caution. It's easy to create a result set with many more rows than you intended, which can lead to performance issues. A CROSS JOIN should only be used when you want to create a result set with all possible combinations of rows from two tables.

For example, let's say you have a colors table and a sizes table. The colors table contains a list of color options, and the sizes table contains a list of size options.

SQL
SELECT colors.color, sizes.size
FROM colors
CROSS JOIN sizes;

This CROSS JOIN returns a result set that includes all possible combinations of colors and sizes. If the colors table has 5 rows and the sizes table has 3 rows, the result set will have 15 rows (5 x 3).

In general, a CROSS JOIN should only be used when you want to create a result set with all possible combinations of rows from two tables.

Next Lesson