Use a SQL CROSS JOIN to combine rows from each table. Unlike other joins, a cross join uses no join conditions. It produces a combination of all rows from the tables joined.
In other words, it produces a cross product of the two tables.
Below is an example of simple select statement with a CROSS JOIN clause.
SELECT column1, column2, ... FROM table1 CROSS JOIN table2
Be careful when using a SQL CROSS JOIN as the combinatory effect is startling. Cross joining a 1000 row table to itself results in a 1,000,000-row result set! Want to dig deeper? check out this article!
SQL CROSS JOIN Example
In this example we’ll use CROSS JOIN to combine results between the sales.Territory table. Since there are 10 territories, and the final results contains a combination of each, there are 100 rows in the result.
Here is the query:
SELECT t1.name, t2.name FROM Sales.SalesTerritory t1 CROSS JOIN Sales.SalesTerritory t2
When working with SQL cross joins keep in mind the row combinations multiply. Meaning, if you cross join three tables with 3,4, and 5 rows respectively, you end up with a 120 row (3 x 4 x 5) result!
Additional SQL Join Resources
To learn more about the joins, check out these useful resources: