How to Get all Possible Combinations of Rows 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 perform the CROSS JOIN 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. In other words, if the first table stores n rows 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.