“cross join” is a difficult to explain to trainees. The mechanics aren’t very complicated. However, the question is where you would ever use them?
One example that I came up with is generating a calendar. Let’s say I want to generate a list of dates in a set of years. For example, I want a query that returns every valid date in the years between 2001 and 2013. I don’t want complicated logic to deal with whether a month has 30 days or 31 days or even 28 days. I don’t want to add any complicated logic that determines if the year is a leap year.
The following script uses common table expressions (CTE) to generate three virtual tables (years, months, days). I use the recursive features of CTEs to create a range of values. The years range from the values set in the variables. The months range from 1 to 12 and days from 1 to 31. I then combine the years, months and days in a final CTE that I call datekeys. That CTE cross joins all of the other three CTEs (years, months, days) and then concatenates the values into an ISO date. Finally, I use the new function for 2012 TRY_CONVERT to convert the datekey (which has been cast to varchar). If the TRY_CONVERT doesn’t return NULL then the date is valid.
declare @startYear int = 2001; declare @endYear int = 2012; with years as (Select @startYear as [Year] union all Select [Year] + 1 from years where [Year] < @endYear) , months as (Select 1 as [Month] union all Select [Month] + 1 from months where [Month] < 12) , days as ( select 1 as [Day] union all select [Day] + 1 from days where [Day] < 31 ) , datekeys as ( select [year] * 10000 + [month] * 100 + [day] as datekey from years cross join months cross join days ) select datekey, try_convert(date, cast(datekey as varchar(8))) as DateValue from datekeys where try_convert(date, cast(datekey as varchar(8))) is not null;
I can’t say whether this is the best performing way to do this, but it quickly generated a date list on my box.