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.