How to Get all Row Combinations From Two Tables in SQL

Problem:

You want to get all combinations of rows from two tables in SQL.

Example:

Here are two tables: one contains letters (letters), and the other contains numbers (numbers):

Solution 1:

In order to combine every row of the letters table with every row of the numbers table, we will use the CROSS JOIN:

SELECT *
FROM letters
CROSS JOIN numbers;

The result of the query looks like this:

Solution 2:

Another way to get all combinations of rows from two tables is as follows:

SELECT *
FROM letters, numbers;

This will get the exact same result produced by the previous query.

Discussion:

As a general rule, a CROSS JOIN produces a result set in which every row from one table is joined to every row of another table. If the first table stores nrows and the second table stores m rows, then a CROSS JOIN will result in a Cartesian product of n × m rows. This is why there are six rows returned by the query in the example above.

Because a CROSS JOIN produces all possible combinations of the rows among the tables being joined, there is no need to specify a relationship. So, unlike other JOINs, there is no ON clause in a CROSS JOIN.

When you list table names in FROM clause, as we did in Solution 2, the database performs CROSS JOIN: it combines all rows from the first table with all rows from the second table. However, it is better to use the CROSS JOIN syntax, since it explicitly shows what we want to do in the query.