Trying to avoid using CROSS JOIN

Question

Trying to avoid using CROSS JOIN

kramaswamy

SSCoach

Points: 18181
More actions
October 26, 2017 at 8:27 am

#340986

Hi all,
I've created thousands upon thousands of stored procs, and I can probably count on one hand the number of times I've had to resort to using a CROSS JOIN in any of them. Just seems like a really, really ugly way to accomplish anything.
So, help me out here if possible! I just can't think of a better way of accomplishing the following. I know it's probably not a huge concern, since although I'm doing a cross join, I'm also explicitly filtering both tables down to one record apiece, but it still seems super ugly.

DECLARE @MasterID INT
DECLARE @ValueID INT
SET @MasterID = 1
SET @ValueID = 1
CREATE TABLE #TableToInsertValuesInto
(
ID INT IDENTITY PRIMARY KEY,
MasterID INT,
Val VARCHAR(200)
)
CREATE TABLE #TableThatHasValues
(
ID INT IDENTITY PRIMARY KEY,
Field1 VARCHAR(200),
Field2 VARCHAR(200),
Field3 VARCHAR(200),
Field4 VARCHAR(200)
)
CREATE TABLE #TableThatHasTemplate
(
ID INT IDENTITY PRIMARY KEY,
MasterID INT,
TemplateName VARCHAR(200)
)
INSERT INTO #TableThatHasValues (Field1, Field2, Field3, Field4)
VALUES ('A', 'B', 'C', 'D')
INSERT INTO #TableThatHasTemplate (TemplateName, MasterID)
VALUES ('SomeValue', 1), ('AnotherValue', 1), ('MoreValues', 1), ('YetMoreValues', 1)
INSERT INTO #TableToInsertValuesInto (MasterID, Val)
SELECT
  #TableThatHasTemplate.MasterID,
  (
   CASE TemplateName
    WHEN 'SomeValue' THEN Field1
    WHEN 'AnotherValue' THEN Field2
    WHEN 'MoreValues' THEN Field3
    WHEN 'YetMoreValues' THEN Field4
   END
  )
FROM #TableThatHasTemplate
CROSS JOIN #TableThatHasValues
WHERE #TableThatHasValues.ID = @ValueID
  AND #TableThatHasTemplate.MasterID = @MasterID
DROP TABLE #TableThatHasTemplate
DROP TABLE #TableThatHasValues
DROP TABLE #TableToInsertValuesInto

Viewing 9 posts - 1 through 8 (of 8 total)

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

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 1

Why do you even need the cross join? Or are you asking how to do that without using a cartesian product, if so what is the problem with that?

SELECT #TableThatHasTemplate.MasterID, ( CASE TemplateName WHEN 'SomeValue' THEN Field1 WHEN 'AnotherValue' THEN Field2 WHEN 'MoreValues' THEN Field3 WHEN 'YetMoreValues' THEN Field4 END ) FROM #TableThatHasTemplate , #TableThatHasValues WHERE #TableThatHasValues.ID = @ValueID AND #TableThatHasTemplate.MasterID = @MasterID

kramaswamy SSCoach Points: 18181 More actions · Answer 2

What you're doing is a CROSS JOIN, just without using the words CROSS JOIN.

By specifying two tables with a comma, SQL Server is implicitly applying a CROSS JOIN to form the query.

**edit

As for why I'm trying to avoid using a Cartesian product, well ... it just seems dirty to me, and I was sorta hoping there was a cleaner way of accomplishing my objective.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

You could drop the CROSS and change the WHERE to ON. Same query, just avoiding the dreaded cross join syntax.

John

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 4

kramaswamy - Thursday, October 26, 2017 8:46 AM
What you're doing is a CROSS JOIN, just without using the words CROSS JOIN.
By specifying two tables with a comma, SQL Server is implicitly applying a CROSS JOIN to form the query.

Right and you are trying to get a cartesian product, why do you think that is a bad thing? You have two tables that aren't directly linked and you want all the results from one table matched with all the results from another table.

drew.allen SSC Guru Points: 76944 More actions · Answer 5

The real issue is that one of your tables isn't normalized. If you normalize your table, you don't need to use a CROSS JOIN.

SELECT t.ID, t.MasterID, v.Val FROM #TableThatHasValues tv CROSS APPLY -- unpivot ( VALUES ('SomeValue', Field1) , ('AnotherValue', Field2) , ('MoreValues', Field3) , ('YetMoreValues', Field4) ) v(TemplateName, Val) INNER JOIN #TableThatHasTemplate t ON v.TemplateName = t.TemplateName

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

kramaswamy SSCoach Points: 18181 More actions · Answer 6

Drew, can you explain that query a bit more? I've never seen that type of operation before. What exactly is the CROSS APPLY doing in this case? And how does that parameterized output for the CROSS APPLY work (the v(TemplateName, Val) part)?

drew.allen SSC Guru Points: 76944 More actions · Answer 7

The part in parentheses is just a Table Value Constructor and the v(TemplateName, Val) is just providing aliases for the fields returned by the Table Value Constructor. The CROSS APPLY allows the Table Value Constructor to access fields from the left table source.

This construct is just doing an UNPIVOT. I prefer this format, because it's much simpler syntax than using the UNPIVOT construct. I believe it's also faster, but I'm not positive on that.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

frederico_fonseca SSCoach Points: 16014 More actions · Answer 8

drew.allen - Thursday, October 26, 2017 11:35 AM
This construct is just doing an UNPIVOT. I prefer this format, because it's much simpler syntax than using the UNPIVOT construct. I believe it's also faster, but I'm not positive on that.
Drew

From my experience it depends - on SQL 2008 R2 on a table with 9 million rows, unpivoting 20 fields was slightly faster - 4-5% faster.
Again as the method above is simpler I used the outer apply syntax.
But there might be cases where timing differences are significant and require any drop of improvement that may warrant the unpivot option.