Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Cartesian product!
Add to briefcase

Cartesian product! Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Viking-204480
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: Tuesday, May 21, 2013 4:44 AM
Points: 42, Visits: 225
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
Jeff Moden
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 @ 7:18 AM
Points: 33,112, Visits: 27,038
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #423320
pdross2000
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: Wednesday, November 07, 2012 2:51 PM
Points: 29, Visits: 80
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
Jeff Moden
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 @ 7:18 AM
Points: 33,112, Visits: 27,038
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #423538
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.