Using Cross Joins

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The following example shows a Transact-SQL cross join.

USE AdventureWorks;
GO
SELECT p.SalesPersonID, t.Name AS Territory
FROM Sales.SalesPerson p
CROSS JOIN Sales.SalesTerritory t
ORDER BY p.SalesPersonID;

The result set contains 170 rows (SalesPerson has 17 rows and SalesTerritory has 10; 17 multiplied by 10 equals 170).

However, if a WHERE clause is added, the cross join behaves as an inner join. For example, the following Transact-SQL queries produce the same result set.

USE AdventureWorks;
GO
SELECT p.SalesPersonID, t.Name AS Territory
FROM Sales.SalesPerson p
CROSS JOIN Sales.SalesTerritory t
WHERE p.TerritoryID = t.TerritoryID
ORDER BY p.SalesPersonID;

-- Or

USE AdventureWorks;
GO
SELECT p.SalesPersonID, t.Name AS Territory
FROM Sales.SalesPerson p
INNER JOIN Sales.SalesTerritory t
ON p.TerritoryID = t.TerritoryID
ORDER BY p.SalesPersonID;

See Also

Concepts

Using Operators in Expressions

Other Resources

WHERE (Transact-SQL)
SELECT (Transact-SQL)
Operators (Transact-SQL)
SELECT Examples (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
14 April 2006	Changed content: Changed the examples to use different tables.