December 17, 2014 at 12:00 am
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
December 17, 2014 at 12:44 am
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
December 17, 2014 at 1:56 am
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
Change is inevitable... Change for the better is not.
December 17, 2014 at 3:31 am
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 haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 17, 2014 at 7:08 am
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
Change is inevitable... Change for the better is not.
December 18, 2014 at 3:02 am
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
December 18, 2014 at 3:11 am
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 haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 19, 2014 at 12:19 pm
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
December 21, 2014 at 2:11 pm
Thanks, Gabe. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
December 29, 2014 at 6:48 am
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.
January 11, 2016 at 8:57 am
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?
January 11, 2016 at 3:07 pm
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!
January 11, 2016 at 7:23 pm
SSCommitted,
Thank you for the reply to the question.
Greg
Gregory A. Larsen, MVP
January 12, 2016 at 7:34 am
Thanks for the reply. I should have read the article more carefully.
January 12, 2016 at 7:38 am
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 haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply