SQL Server – Get Matrix for two different result sets

I had a need today to splice 2 different result sets together and effectively give all permutations of the two tables.  I’d seen different examples on doing this with cursors and loops etc, but I thought – there ‘must’ be an easier way – there’s lots of different join types right?


The first issue I’d got is that there’s no common fields between the two tables I need to merge because otherwise a ‘simple’ FULL OUTER JOIN would do the biz.


In the end it didn’t amount to much more than that because a slightly dodgy hack (but functional nontheless), to guarantee full coverage from the join is to simply place the same literal on both sides of the join 🙂


With 2 sets of ‘different’ data (let’s use Northwind as I’m not about to bore you with my own data).  Say you wanted to generate a list of all products for all regions given the following result sets…


SELECT ProductName
FROM Products


SELECT RegionDescription
FROM Region


You can splice them together with this….


SELECT  R.RegionDescription,
        P.ProductName
FROM Products P
FULL OUTER JOIN Region R
    ON 1 = 1


and you get something like this… (snippet)


RegionDescription                                  ProductName                             
————————————————– —————————————-
Eastern                                            Valkoinen suklaa
Eastern                                            Vegie-spread
Eastern                                            Wimmers gute Semmelknödel
Eastern                                            Zaanse koeken
Western                                            Alice Mutton
Western                                            Aniseed Syrup
Western                                            Boston Crab Meat


If there’s actually a common field then your join can obviously use that.  Any other filtering you want to do can also go into the WHERE clause as required.


This is another reminder of the lesson that if you’re tempted to use a CURSOR, then resist that urge until you’ve exhausted your Google finger!


SQLServerCentral and SQLMag often have the answer you’re looking for.