We recently had a problem where we needed to bring back a recordset with a row for every day ( between a range ) and every employee, regardless if the employee had data for that date. In order to accomplish this I used a cartesian product. Half the staff thought I was crazy and was breaking every rule of SQL. I knidly pointed out that in certain situations, like this one, it was the easiest way to solve the problem, and the Cross Join is the most basic of all joins.
I would be interested in hearing some other situations where a cross join was used.