The strangest SQL I've ever written

[rhu]

SELECT
-- various aggregation functions
FROM RecipientGroup g
CROSS JOIN 
  (SELECT 0 AS b_UsingTestData UNION SELECT 1) AS TestDataFlag
LEFT OUTER JOIN
  RecipientData rd ON  rd.GroupID=g.GroupID 
                   AND rd.b_IsTestRecord = TestDataFlag.b_UsingTestData
GROUP BY
  g.GroupID, TestDataFlag.b_UsingTestData
ORDER BY
  g.GroupID, TestDataFlag.b_UsingTestData

OK, SQLheads, is there a better way to ensure that every group's aggregate data includes rows for both the test records and the live records, even if a particular group only has test or live data? Because that cross join in bold feels like it's either incredibly elegant and idiomatic or the ugliest kludgy hack ever, and I'm too close to it to be able to tell which. (This is for Microsoft SQL Server 2000 and 2005)

Comments

[530nm330hz.livejournal.com]

You haven't eliminated the union across a fake data value, you've just made its interpretation a little more explicit. But yours is less efficient because the left outer join gets executed twice (or the optimizer has to work a little harder to figure out that yours and mine are equivalent queries.)

[sethg]

I was going for more clarity, not more efficiency.

I'm not sure how (if at all) the original query could be made more efficient, although if I knew more about the tables and the aggregation functions being used, I might get some ideas. (E.g., I recently rewrote a nasty chunk of our own SQL codebase, introducing user-defined aggregates to prevent multiple traversals of the same table.)