none
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;
    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;

    why have the cross join, ie using

    USE AdventureWorks;
    GO
    SELECT p.SalesPersonID, t.Name AS Territory
    FROM Sales.SalesPerson p,
    Sales.SalesTerritory t
    WHERE p.TerritoryID = t.TerritoryID
    ORDER 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

Answers

  • 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

    USE AdventureWorks;

    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

     

    Let us know if helpful.

     

    Sunday, November 09, 2008 11:50 AM
    Moderator
  •  

    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
    Moderator

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

    USE AdventureWorks;

    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

     

    Let us know if helpful.

     

    Sunday, November 09, 2008 11:50 AM
    Moderator
  •  

    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
    Moderator