SQL CROSS JOIN

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
SELECT  t1.name, 
        t2.name 
FROM   Sales.SalesTerritory t1 
CROSS JOIN Sales.SalesTerritory t2 

Important Points: 

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: 

>