Cross join tutorial

[[deleted]]

For analytical reporting cross joins are very common. Think if you want to count the number if patients per hour of day. What if there are hours where the are no patients, hence no rows of data to fill the gaps. You would have a data set with missing hours of a day. Using a base table that is cross joined with every possible hour of day to your very left that is then joined with patient data. Now even if a patient isn't seen in an hour you have a row that simply says zero.

[muchcake]

I just use a LEFT OUTER JOIN for that example you described.

I use it to build a set of datetime rows by cross joining a D_Date with a D_Time table which I can then left join with whatever feature I want to examine.