"I am not sure of a real business need for a Cartesian product"

I have seen a pretty useful implementation of a Cartesian product.

Have you ever had a report where you need a series of data grouped by a specific criteria in the series in the data (foreign keys), then by Days, weeks, or months. But when ever there was no data for the day week or month it would simply be missing from your report? Well that is fixed by creating a pretty simple static table to store your dates in. Then with a cross join on your series and your dates you will get a product all of the dates for each of your series even though they are unrelated you still get use out of their product. Eg

Table IDs

Dates

You can now cross join (Select * from IDs Cross Join Dates)

now that you have this nice neat Cartesian product you can include a left join to the table with your meat and get your statistics.

Select IDs.ID, Dates.Date, IsNull(Count(meat.id), 0) from IDs Cross Join Dates Left Join meat on meat.id = ids.ID and meat.date = dates.date Group by IDs.ID, Dates.Date

It works out pretty nice when aggregating data for 3 dimensional charting apps.

Try it out

Select

1 as ID into #ids

insert

into #ids values (2)

insert

into #ids values (3)

Select

'2007-04-01 00:00:00.000' as date into #dates

insert

into #dates values ('2007-04-02 00:00:00.000')

insert

into #dates values ('2007-04-03 00:00:00.000')

Select

1 as id, '2007-04-03 00:00:00.000' as date into #meat

insert

into #meat (id, date) values (1,'2007-04-03 00:00:00.000')

insert

into #meat (id, date) values (1,'2007-04-03 00:00:00.000')

insert

into #meat (id, date) values (1,'2007-04-01 00:00:00.000')

insert

into #meat (id, date) values (2,'2007-04-03 00:00:00.000')

insert

into #meat (id, date) values (2,'2007-04-03 00:00:00.000')

insert

into #meat (id, date) values (2,'2007-04-02 00:00:00.000')

insert

into #meat (id, date) values (2,'2007-04-02 00:00:00.000')

insert

into #meat (id, date) values (3,'2007-04-01 00:00:00.000')

Select

#ids.id, #dates.date, isnull(count(#meat.id),0) as ID_Date_Count

from

#ids cross join #dates

Left join #meat on #meat.id = #ids.id and #meat.date = #dates.date

Group

by #ids.id, #dates.date

Drop

table #ids

Drop

table #date

Drop

table #meat

Sorry for the bad markup but im not just #'n meat this is pretty useful.