sql cross join - what use has anyone found for it?

Question

Today, for the first time in 10 years of development with sql server I used a cross join in a production query. I needed to pad a result set to a report and found that a cross join between two tables with a creative where clause was a good solution. I was wondering what use has anyone found in production code for the cross join?

Update: the code posted by Tony Andrews is very close to what I used the cross join for. Believe me, I understand the implications of using a cross join and would not do so lightly. I was excited to have finally used it (I'm such a nerd) - sort of like the time I first used a full outer join.

Thanks to everyone for the answers! Here's how I used the cross join:

SELECT  CLASS, [Trans-Date] as Trans_Date,
SUM(CASE TRANS
     WHEN 'SCR' THEN [Std-Labor-Value]
     WHEN 'S+' THEN [Std-Labor-Value]
     WHEN 'S-' THEN [Std-Labor-Value]
     WHEN 'SAL' THEN [Std-Labor-Value]
     WHEN 'OUT' THEN [Std-Labor-Value]
     ELSE 0
END) AS [LABOR SCRAP],
SUM(CASE TRANS
     WHEN 'SCR' THEN  [Std-Material-Value]
     WHEN 'S+' THEN [Std-Material-Value]
     WHEN 'S-' THEN  [Std-Material-Value]
     WHEN 'SAL' THEN [Std-Material-Value]
     ELSE 0
END) AS [MATERIAL SCRAP], 
SUM(CASE TRANS WHEN 'RWK' THEN [Act-Labor-Value] ELSE 0 END) AS [LABOR REWORK],
SUM(CASE TRANS 
     WHEN 'PRD' THEN  [Act-Labor-Value]
     WHEN 'TRN' THEN  [Act-Labor-Value]
     WHEN 'RWK' THEN  [Act-Labor-Value]
     ELSE 0 
END) AS [ACTUAL LABOR],
SUM(CASE TRANS 
     WHEN 'PRD' THEN  [Std-Labor-Value]
     WHEN 'TRN' THEN   [Std-Labor-Value]
     ELSE 0 
END) AS [STANDARD LABOR],
SUM(CASE TRANS 
     WHEN 'PRD' THEN  [Act-Labor-Value] - [Std-Labor-Value]
     WHEN 'TRN' THEN  [Act-Labor-Value] - [Std-Labor-Value]
     --WHEN 'RWK' THEN  [Act-Labor-Value]
     ELSE 0 END) -- - SUM([Std-Labor-Value]) -- - SUM(CASE TRANS WHEN 'RWK' THEN [Act-Labor-Value] ELSE 0 END) 
AS [LABOR VARIANCE] 
FROM         v_Labor_Dist_Detail
where [Trans-Date] between @startdate and @enddate
    --and CLASS = (CASE @class WHEN '~ALL' THEN CLASS ELSE @class END)
GROUP BY  [Trans-Date], CLASS
UNION  --REL 2/6/09 Pad result set with any missing dates for each class. 
select distinct [Description] as class,  cast([Date] as datetime) as [Trans-Date], 0,0,0,0,0,0 
FROM Calendar_To_Fiscal cross join PRMS.Product_Class
where cast([Date] as datetime) between @startdate and @enddate and
not exists (select class FROM v_Labor_Dist_Detail vl where [Trans-Date] between @startdate and @enddate
        and vl.[Trans-Date] = cast(Calendar_To_Fiscal.[Date] as datetime)
        and vl.class= PRMS.Product_Class.[Description]
       GROUP BY [Trans-Date], CLASS)
order by [Trans-Date], CLASS

Answer 1

I personally try to avoid cartesian product's in my queries. I suppose have a result set of every combination of your join could be useful, but usually if I end up with one, I know I have something wrong.

Answer 2

I have different reports that prefilter the recordset (by various lines of business within the firm), but there were calculations that required percentages of revenue firm-wide. The recordsource had to contain the firm total instead of relying on calculating the overall sum in the report itself.

Example: The recordset has balances for each client and the Line of Business the client's revenue comes from. The report may only show 'retail' clients. There is no way to get a sum of the balances for the entire firm, but the report shows the percentage of the firm's revenue.

Since there are different balance fields, I felt it was less complicated to have full join with the view that has several balances (I can also reuse this view of firm totals) instead of multiple fields made up sub queries.

Another one is an update statement where multiple records needed to be created (one record for each step in a preset workflow process).

Answer 3

A typical legitimate use of a cross join would be a report that shows e.g. total sales by product and region. If no sales were made of product P in region R then we want to see a row with a zero, rather than just not showing a row.

select r.region_name, p.product_name, sum(s.sales_amount)
from regions r
cross join products p
left outer join sales s on  s.region_id = r.region_id
                        and s.product_id = p.product_id
group by r.region_name, p.product_name
order by r.region_name, p.product_name;

Answer 4

One use I've come across a lot is splitting records out into several records, mainly for reporting purposes.

Imagine a string where each character represents some event during the corresponding hour.

ID | Hourly Event Data
1  | -----X-------X-------X--
2  | ---X-----X------X-------
3  | -----X---X--X-----------
4  | ----------------X--X-X--
5  | ---X--------X-------X---
6  | -------X-------X-----X--

Now you want a report which shows how many events happened at what day. Cross join the table with a table of IDs 1 to 24, then work your magic...

SELECT
   [hour].id,
   SUM(CASE WHEN SUBSTRING([data].string, [hour].id, 1) = 'X' THEN 1 ELSE 0 END)
FROM
   [data]
CROSS JOIN
   [hours]
GROUP BY
   [hours].id

=>

1,  0
2,  0
3,  0
4,  2
5,  0
6,  2
7,  0
8,  1
9,  0
10, 2
11, 0
12, 0
13, 2
14, 1
15, 0
16, 1
17, 2
18, 0
19, 0
20, 1
21, 1
22, 3
23, 0
24, 0

Answer 5

I've used a CROSS JOIN recently in a report that we use for sales forcasting, the report needs to break out the amount of sales that a sales person has done in each General Ledger account.

So in the report I do something to this effect:

SELECT gla.AccountN, s.SalespersonN
FROM
    GLAccounts gla
    CROSS JOIN Salesperson s
WHERE (gla.SalesAnalysis = 1 OR gla.AccountN = 47500)

This gives me every GL account for every sales person like:

SalesPsn    AccountN
1000    40100
1000    40200
1000    40300
1000    48150
1000    49980
1000    49990
1005    40100
1005    40200
1005    40300
1054    48150
1054    49980
1054    49990
1078    40100
1078    40200
1078    40300
1078    48150
1078    49980
1078    49990
1081    40100
1081    40200
1081    40300
1081    48150
1081    49980
1081    49990
1188    40100
1188    40200
1188    40300
1188    48150
1188    49980
1188    49990