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
Change is inevitable... Change for the better is not.