Cartesian product!

Question

Cartesian product!

Viking-204480

SSC Eights!

Points: 970
More actions
November 17, 2007 at 6:00 am

#179354

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 :crazy:. 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

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pdross2000 Old Hand Points: 363 More actions · Answer 2

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'

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

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 😀

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)