# How to Get all Possible Combinations of Rows From two Tables in SQL

Database:

Operators:

#### Problem:

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

#### Example:

Here are two tables: one contains letters (

), and the other contains numbers (**letters**

):**numbers**

letter |
---|

X |

Y |

number |
---|

0 |

1 |

2 |

#### Solution 1:

In order to combine every row of the

table with every row of the **letters**

table, we will use the **numbers**`CROSS JOIN`

:

SELECT * FROM letters CROSS JOIN numbers;

The result of the query looks like this:

letter | number |
---|---|

X | 0 |

Y | 0 |

X | 1 |

Y | 1 |

X | 2 |

Y | 2 |

#### 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`

.