Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cartesian product! Expand / Collapse
Author
Message
Posted Saturday, November 17, 2007 6:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 14, 2014 4:45 AM
Points: 42, Visits: 235
I am working on some kind of requirement and here is what I need to do.

I have two tables

TabA
Col11 Col1 Col2
A 1 2
A 1 3
A 2 4

TabB
Col11 Col3 Col4
A 11 21
A 11 31
A 12 41

I would need results as

1 2 11 21
1 2 11 31
1 2 12 41
1 3 11 21
1 3 11 31
1 3 12 41
2 4 11 21
2 4 11 31
2 4 12 41

Alright, this looks simple when thought. It is like multiplication, and I could do it with "dreaded cartesian product" CROSS JOIN. This is crazy stuff. I am looking at tables that could have anywhere from 1 - 1 million rows in each table . It is just all possible combinations should be given in the resultset for particular value A.

Here was my query

select x.col1, x.col2, y.col3, y.col4
from TabA as x
cross join
TabB as Y
where x.col11 = 'A' and y.col11 = 'A'

Can someone help me in finding a way out without using CROSS JOIN?

Thanks in advance!




Thanks!

Viking
Post #423318
Posted Saturday, November 17, 2007 7:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:42 PM
Points: 35,768, Visits: 32,431
Alright, this looks simple when thought. It is like multiplication, and I could do it with "dreaded cartesian product" CROSS JOIN. This is crazy stuff. I am looking at tables that could have anywhere from 1 - 1 million rows in each table . It is just all possible combinations should be given in the resultset for particular value A.

Here was my query

select x.col1, x.col2, y.col3, y.col4
from TabA as x
cross join
TabB as Y
where x.col11 = 'A' and y.col11 = 'A'

Can someone help me in finding a way out without using CROSS JOIN?


Sure... you could use nested WHILE loops... perhaps even nested CURSORs... but you problem definition screams "use a cross-join"... and the cross-join will be hundreds of times faster than anything else you use.

If your intent is to simply avoid the word "Cross-Join" to get by any automated code checking that looks for the words "CROSS JOIN", then you could use the following...

select x.col1, x.col2, y.col3, y.col4
from TabA as x,
TabB as Y
where x.col11 = 'A' and y.col11 = 'A'

And, as you know, cross-joining tables with millions of rows can produce internal working tables in the tera-row category that will drive TempDB to the edges of disk capacity.

But, the bottom line is, if you need "row multiplication" as you've described, the a cross-join is the single most effective method.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #423320
Posted Sunday, November 18, 2007 10:20 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:14 PM
Points: 29, Visits: 85
why no inner join?

select x.col1, x.col2, y.col3, y.col4
from TabA as x
inner join
TabB as Y
on x.col11 = y.coll11
where x.col11 = 'A'
Post #423431
Posted Monday, November 19, 2007 5:47 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:42 PM
Points: 35,768, Visits: 32,431
That certainly looks like an inner join... but, it's not... behind the scenes, you end up with the same thing as a cross-join because of the criteria you've specified.

It will, however, deceive a DBA that's in a hurry :D


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #423538
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse