Stairway to Advanced T-SQL Level 1: Intro to Advanced T-SQL Using a CROSS JOIN

  • Comments posted to this topic are about the item Stairway to Advanced T-SQL Level 1: Intro to Advanced T-SQL Using a CROSS JOIN

    Gregory A. Larsen, MVP

  • Hi

    It is not often I use the cross join, for obvious reasons, however, I hadn't thought about using it to identify records that have no match in another table before. I will put that away to consider using in the future.

    I did have cause to use the cross join recently. This was to build a table containing all the positions in laboratory sample boxes; an extract of the code below:

    SELECT Y + X AS Position

    FROM (VALUES ('01'), ('02'), ('03'), ('04'), ('05'), ('06'), ('07'), ('08'), ('09'), ('10')) AS X(X)

    CROSS JOIN (VALUES ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'), ('H'), ('I'), ('J')) AS Y(Y)

    This was far superior in every way from the nested while loops I had previously created to achieve the same thing.

    John

  • From the Article:


    CREATE TABLE Cust (Id int, CustName varchar(20));

    CREATE TABLE Sales (Id int identity

    ,CustID int

    ,SaleDate date

    ,SalesAmt money);

    SET NOCOUNT ON;

    DECLARE @I int = 0;

    DECLARE @Date date;

    WHILE @I < 100000 --<<<<CHANGE THIS TO 100,000

    BEGIN

    SET @I = @I + 1;

    SET @Date = DATEADD(mm, -2, '2014-11-01');

    INSERT INTO Cust

    VALUES (@I,

    'Customer #' + right(cast(@I+100000 as varchar(6)),5));

    WHILE @Date < '2014-11-01'

    BEGIN

    IF @I%7 > 0

    INSERT INTO Sales (CustID, SaleDate, SalesAmt)

    VALUES (@I, @Date, 10.00);

    SET @Date = DATEADD(DD, 1, @Date);

    END

    END

    Listing 7: TSQL to create sample data for performance test

    Nice article, Greg. Let's even turn the test data generator you wrote above into a performance test. I changed it to generate 100,000 customers and then used CROSS JOINs below to do the same thing.

    SET NOCOUNT OFF;

    --===== Create and populate the "Cust" table on the fly using CROSS JOINs

    WITH --Similar to Itzik Ben-Gan's method

    E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))x(N))

    ,E3(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c)

    ,E9(N) AS (SELECT 1 FROM E3 a, E3 b, E3 c)

    ,cteTally(N) AS (SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E9)

    SELECT ID = CAST(N AS INT)

    ,CustName = CAST('Customer #' + RIGHT(N+1000000,6) AS VARCHAR(20))

    INTO dbo.Cust

    FROM cteTally

    ;

    --===== Create and populate the "Sales" table on the fly using CROSS JOINs

    WITH --Old fashioned but simple method

    cteTally AS

    (

    SELECT TOP (DATEDIFF(dd,'Sep 2014','Nov 2014'))

    N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1

    FROM sys.all_columns ac1

    CROSS JOIN sys.all_columns ac2

    )

    SELECT CustID = c.ID

    ,SaleDate = DATEADD(dd,t.N,'Sep 2014')

    ,SalesAmt = 10.00 --Kept this the same as yours. Could be random

    INTO dbo.Sales

    FROM cteTally t

    CROSS JOIN dbo.Cust c

    ;

    CROSS JOINs ROCK! :w00t:

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • The article is absolutely fine, though I would not categorise a CROSS JOIN as advanced.

    AFAIK, the words CROSS JOIN can be replaced by a single , and all will work fine (and we get back to old ANSI SQL join syntax). John's query then becomes

    SELECT Y + X AS Position

    FROM (VALUES ('01'), ('02'), ('03'), ('04'), ('05'), ('06'), ('07'), ('08'), ('09'), ('10')) AS X(X),

    (VALUES ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'), ('H'), ('I'), ('J')) AS Y(Y)

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • Phil Parkin (12/17/2014)


    ... though I would not categorise a CROSS JOIN as advanced.

    Heh... you're also not a neophyte, Phil. All the "heavy hitters" take it for granted. Look how many posts are solved with some form of CROSS JOIN and the revelation that the original posters had when they were shown. Remember back to the moment of revelation when you first found out/realized that you could do all of this.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • And there is yet another variant:

    SELECT Y + X AS Position

    FROM (VALUES ('01'), ('02'), ('03'), ('04'), ('05'), ('06'), ('07'), ('08'), ('09'), ('10')) AS X(X)

    join

    (VALUES ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'), ('H'), ('I'), ('J')) AS Y(Y)

    on 1=1

  • Michael Meierruth (12/18/2014)


    And there is yet another variant:

    SELECT Y + X AS Position

    FROM (VALUES ('01'), ('02'), ('03'), ('04'), ('05'), ('06'), ('07'), ('08'), ('09'), ('10')) AS X(X)

    join

    (VALUES ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'), ('H'), ('I'), ('J')) AS Y(Y)

    on 1=1

    Ideal for those times when you want to puzzle the people who will have to support your code 🙂

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • Jeff Moden (12/17/2014)


    Phil Parkin (12/17/2014)


    ... though I would not categorise a CROSS JOIN as advanced.

    Heh... you're also not a neophyte, Phil. All the "heavy hitters" take it for granted. Look how many posts are solved with some form of CROSS JOIN and the revelation that the original posters had when they were shown. Remember back to the moment of revelation when you first found out/realized that you could do all of this.

    Seeing Jeff's post reminds me, while I'm not an advanced SQL code writer, I'm also no longer a neophyte...but I DO recall the day I discovered the first real-world use of CROSS JOIN and I haven't ever forgot it. It was my introduction to a Tally table.

    <Tips hat to Jeff>

    Gabe

  • Thanks, Gabe. 🙂

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Thank you for this new Stairway series Greg. I think it is a great idea, and helpful for those like studying for the 70-461 exam.

    One minor typo: At the very beginning, you write, " ... that you learned in the prior two TSQL stairways:" I believe the first bullet that follows that should read "Stairway to T-SQL: DML (not DLM). The actual hyperlink is correct.

    Regards,

    John B.

  • For Listing 3, you calculate 'cost' as product cost divided by SalesItem.ID, which makes no sense to me:

    (Product.Cost / SalesItem.ID) * 100 AS Cost

    Can you explain the reasoning for this? How does the ID of the sales item have anything to do with the cost? Or is SalesItem.ID the wrong column by which to divide?

  • That code is not actually retrieving/calculating the cost of some existing product; he's just using that to generate unique costs for each item.

    The idea is that you can use constructions like that to generate varied sample data; a SELECT like that could be used as the source of an INSERT to populate a test table with sample data.

    He indicates that just a bit below the listing:

    Additionally I used the ID column from my SalesItem table to create the unique ProductName, and Cost column values.

    Cheers!

  • SSCommitted,

    Thank you for the reply to the question.

    Greg

    Gregory A. Larsen, MVP

  • Thanks for the reply. I should have read the article more carefully.

  • Greg Larsen (1/11/2016)


    SSCommitted,

    Thank you for the reply to the question.

    Greg

    With over 1,000 posts, you should know by now that 'SSCommitted' is not Jacob's name 🙂

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply