A cross join, also known as Cartesian Product in SQL programming, between two tables joins each row from one table with each row of the other table. The number of rows in the result set is the number of rows in the first table multiplied by the number of rows in the second table.
This implies that if Table A has 10 rows and Table B has 5 rows, then all 10 rows of Table A are joined with all 5 rows of Table B. therefore, the result set will contain 50 rows.
Consider following query in which all the BusinessEntityID is retrieved from SalesPerson table and SalesTerritory with their cross join. The where condition will filter the result according to the territoryID, and at the last the result will sort by the resulting BusinessEntityID.
SELECT p.BusinessEntityID, t.Name AS Territory
FROM Sales.SalesPerson p
CROSS JOIN Sales.SalesTerritory t
WHERE p.TerritoryID = t.TerritoryID
ORDER BY p.BusinessEntityID
The preceding query combines the records of both the tables to display the result with all the possible combinations, as shown in the following figure.
This implies that if Table A has 10 rows and Table B has 5 rows, then all 10 rows of Table A are joined with all 5 rows of Table B. therefore, the result set will contain 50 rows.
Consider following query in which all the BusinessEntityID is retrieved from SalesPerson table and SalesTerritory with their cross join. The where condition will filter the result according to the territoryID, and at the last the result will sort by the resulting BusinessEntityID.
SELECT p.BusinessEntityID, t.Name AS Territory
FROM Sales.SalesPerson p
CROSS JOIN Sales.SalesTerritory t
WHERE p.TerritoryID = t.TerritoryID
ORDER BY p.BusinessEntityID
The preceding query combines the records of both the tables to display the result with all the possible combinations, as shown in the following figure.
