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

Question

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

Greg Larsen

SSC-Insane

Points: 20961
More actions
December 17, 2014 at 12:00 am

#313873

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

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

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

John Corkett SSCrazy Points: 2276 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 2

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)

Phil Parkin SSC Guru Points: 246970 More actions · Answer 3

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)

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 4

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)

Michael Meierruth SSChampion Points: 10051 More actions · Answer 5

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

Phil Parkin SSC Guru Points: 246970 More actions · Answer 6

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 🙂

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

thisisfutile Hall of Fame Points: 3668 More actions · Answer 7

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.

Gabe

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 8

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)

John Bigler SSC-Addicted Points: 450 More actions · Answer 9

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.

paul.abrams 26012 SSC Rookie Points: 42 More actions · Answer 10

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?

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 11

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!

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 12

SSCommitted,

Thank you for the reply to the question.

Greg

Gregory A. Larsen, MVP

paul.abrams 26012 SSC Rookie Points: 42 More actions · Answer 13

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

Phil Parkin SSC Guru Points: 246970 More actions · Answer 14

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 🙂

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel