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

 Cartesian product! Rate Topic Display Mode Topic Options
Author
 Message
 Posted Saturday, November 17, 2007 6:00 AM
 SSC 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 TabACol11 Col1 Col2A 1 2A 1 3A 2 4TabBCol11 Col3 Col4A 11 21A 11 31A 12 41I would need results as1 2 11 211 2 11 311 2 12 411 3 11 211 3 11 311 3 12 412 4 11 212 4 11 312 4 12 41Alright, 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 queryselect x.col1, x.col2, y.col3, y.col4from TabA as xcross joinTabB as Ywhere 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-Forever Group: General Forum Members Last Login: Today @ 9:27 AM Points: 42,046, Visits: 39,430
 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 queryselect x.col1, x.col2, y.col3, y.col4from TabA as xcross joinTabB as Ywhere 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.col4from TabA as x,TabB as Ywhere 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." Helpful Links:How to post code problemsHow to post performance problems
Post #423320
 Posted Sunday, November 18, 2007 10:20 PM
 SSC Rookie Group: General Forum Members Last Login: Wednesday, January 28, 2015 5:38 AM Points: 29, Visits: 86
 why no inner join?select x.col1, x.col2, y.col3, y.col4from TabA as xinner joinTabB as Yon x.col11 = y.coll11where x.col11 = 'A'
Post #423431
 Posted Monday, November 19, 2007 5:47 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 9:27 AM Points: 42,046, Visits: 39,430
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #423538

 Permissions