# Basic cross join question

### Question

• Have a very basic question surrounding the 'cross join' option and really as to whether it's needed. so in the following example sql

`USE AdventureWorks;GOSELECT p.SalesPersonID, t.Name AS TerritoryFROM Sales.SalesPerson pCROSS JOIN Sales.SalesTerritory tWHERE p.TerritoryID = t.TerritoryIDORDER BY p.SalesPersonID;why have the cross join, ie usingUSE AdventureWorks;GOSELECT p.SalesPersonID, t.Name AS TerritoryFROM Sales.SalesPerson p,Sales.SalesTerritory tWHERE p.TerritoryID = t.TerritoryIDORDER BY p.SalesPersonID;would bring back the same result, so why is CROSS JOIN actually needed - does it simply make the SQL easier to read or am i missing something more fundamental?`

Sunday, November 09, 2008 10:56 AM

• You can create Cartesian Product of 2 tables with CROSS JOIN. If you remove the WHERE filter from the CROSS JOIN query, you will see a combinatorical explosion in results:

Code Snippet

GO

SELECT count(*) from Sales.SalesPerson

-- 17

SELECT count(*) from Sales.SalesTerritory

-- 10

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;

-- 170

-- COMBINATORICAL EXAMPLE

declare @Digit table (Digit tinyint)

insert @Digit values (1)

insert @Digit values (2)

insert @Digit values (3)

insert @Digit values (4)

insert @Digit values (5)

insert @Digit values (6)

insert @Digit values (7)

insert @Digit values (8)

insert @Digit values (9)

insert @Digit values (0)

select NumbersTo1000 = H.Digit * 100 + T.Digit * 10 + S.Digit + 1

from @Digit H

cross join @Digit T

cross join @Digit S

order by NumbersTo1000

Partial results:

NumbersTo1000
1
2
3
4
5
6

Sunday, November 09, 2008 11:50 AM
•

In your query you used the "WHERE p.TerritoryID = t.TerritoryID"  beacuse of that it no longer remain a CROSS JOIN.

I think you need to brush up your SQL JOIN Knowledge.

Go through the books online Join Fundamentals

And also go through this on why to use new ANSI JOINS over old style join syntax

ANSI JOIN

Sunday, November 09, 2008 2:20 PM

### All replies

• You can create Cartesian Product of 2 tables with CROSS JOIN. If you remove the WHERE filter from the CROSS JOIN query, you will see a combinatorical explosion in results:

Code Snippet

GO

SELECT count(*) from Sales.SalesPerson

-- 17

SELECT count(*) from Sales.SalesTerritory

-- 10

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;

-- 170

-- COMBINATORICAL EXAMPLE

declare @Digit table (Digit tinyint)

insert @Digit values (1)

insert @Digit values (2)

insert @Digit values (3)

insert @Digit values (4)

insert @Digit values (5)

insert @Digit values (6)

insert @Digit values (7)

insert @Digit values (8)

insert @Digit values (9)

insert @Digit values (0)

select NumbersTo1000 = H.Digit * 100 + T.Digit * 10 + S.Digit + 1

from @Digit H

cross join @Digit T

cross join @Digit S

order by NumbersTo1000

Partial results:

NumbersTo1000
1
2
3
4
5
6

Sunday, November 09, 2008 11:50 AM
•

In your query you used the "WHERE p.TerritoryID = t.TerritoryID"  beacuse of that it no longer remain a CROSS JOIN.

I think you need to brush up your SQL JOIN Knowledge.

Go through the books online Join Fundamentals

And also go through this on why to use new ANSI JOINS over old style join syntax

ANSI JOIN

Sunday, November 09, 2008 2:20 PM