CROSS JOIN

While this join type is listed first in the JOINs submenu, in my experience, it’s the least used type. A CROSS JOIN of two tables or table-like database objects results in a Cartesian product. A what? Let’s first understand the result of a Cartesian product and then dive into a real-world problem where a CROSS JOIN shines. Consider the simple example below where there exists two tables: shirt and pant. The shirt table contains four rows of data, one for each possible color. Likewise, the pant table contains four rows of data, one for each possible color.

The Cartesian product of these two tables would result in the following output:

You may quickly notice that for each shirt color, there is one row for each possible pant color and vice versa. That’s because the Cartesian product of these two tables combines each row from the shirt table with each row from the pant table, resulting in sixteen rows. Use caution when performing CROSS JOINs as they can potentially yield very large and sometimes unintended result sets.

Real-world Problem
CROSS JOINs are particularly useful in instances where gaps exist in your data and it’s desirable to fill them. These gaps tend to exist in temporal data, such as customer purchase dates, rainfall patterns, and hiring practices. Really, anything where data is captured over time. Let’s set the stage for understanding our first real-world problem and solution. The data used throughout this example and captured in the two tables below can be found at livesql.oracle.com.

Orders
The orders table contains one row per order submitted by a customer.

Order Items
This table contains one row per order, per item in an order.

Monthly Sales Representative Performance
In this problem, company sales representatives are responsible for working with customers to generate sales through customer order submissions. Company management wishes to understand sales representative performance on a monthly basis by examining the number of orders placed by customers through each sales representative and the corresponding product quantities and sales. Management has also expressed it’s important to highlight and understand those months where sales representatives were not involved in any customer order submissions. Given this problem, let’s get started.

We’ll start off with simply generating the data used to fill the temporal gaps. This is important because data is generated when a customer submits an order, not when they do not, so we’ll need to take the necessary steps to ensure months where a sales representative was inactive are included in the data. This data generation process relies on using a recursive common table expression (CTE). To follow along, it is not required to be familiar with the concept; however, if you’d like to read more on recursive common table expressions, see the recursion section.

Generating the Data
The recursive CTE below returns one row per sales representative and month combination for those months between the month of the first order and most recent order submitted. Both date endpoints are included. By specifying a fixed starting point and a dynamic end point for reporting month values, we can ensure the minimal number of rows are generated and our code dedicated to capturing the most recent reporting month is not hardcoded. Hardcoding the end point would require manual intervention for each month that passes. That wouldn’t be ideal as it would increase the cost of maintaining the solution. An additional optimization could be implemented by generating reporting month values for sales representatives starting with their individual hire dates instead of starting with the date of the first order submitted. This page is meant to go in-depth on employing low-maintenance and/or optimized solutions, so let’s carry on.

WITH calendar (reporting_month) AS (
    SELECT
    	TRUNC(MIN(oe.orders.order_date), 'MONTH')
    FROM
    	oe.orders
    UNION ALL
    SELECT
    	ADD_MONTHS(calendar.reporting_month, 1)
    FROM
    	calendar
    WHERE
    	calendar.reporting_month < (
    	SELECT
    		TRUNC(MAX(oe.orders.order_date), 'MONTH')
    	FROM
    		oe.orders
    )
),
sales_representative AS (
    SELECT
    	DISTINCT oe.orders.sales_rep_id
    FROM
    	oe.orders
    WHERE
    	oe.orders.sales_rep_id IS NOT NULL
)
SELECT
	sales_representative.sales_rep_id,
    calendar.reporting_month
FROM
	calendar
    CROSS JOIN
    sales_representative;

In the result set above, there exists 486 rows. Why? There are nine sales representatives and 54 months between March 2004 and August 2008. In other words, one row per sales representative and reporting month combination. We’ll use this output as a template where the existing sales data for each sales representative will be populated. Before we can do that, we must first develop a query to capture this data for each sales representative.

Capturing Monthly Sales Data
Below is the query we’ll use to gather the number of orders, product quantities, and sales totals corresponding to each sales representative and order month.

WITH monthly_sales_summary AS (
    SELECT
		oe.orders.sales_rep_id,
        TRUNC(oe.orders.order_date, 'MONTH') AS order_month,
		COUNT(DISTINCT oe.orders.order_id) AS order_count,
        SUM(oe.order_items.quantity) AS quantity_total,
        SUM(oe.order_items.quantity * oe.order_items.unit_price) AS sales_total
    FROM
        oe.orders
        INNER JOIN
        oe.order_items
            ON oe.orders.order_id = oe.order_items.order_id
        	AND oe.orders.sales_rep_id IS NOT NULL
    GROUP BY
		oe.orders.sales_rep_id,
        TRUNC(oe.orders.order_date, 'MONTH')
)
SELECT
	monthly_sales_summary.sales_rep_id,
    monthly_sales_summary.order_month,
    monthly_sales_summary.order_count,
    monthly_sales_summary.quantity_total,
    monthly_sales_summary.sales_total
FROM
	monthly_sales_summary;

Combining the Partial Solutions
Neither of the two queries above independently address management’s reporting requirements. We’ll need to combine the two queries by utilizing a LEFT JOIN. The result of the LEFT JOIN operation with the monthly_sales_template on the left and monthly_sales_summary on the right, is still one row per sales representative and reporting month combination; however, within those reporting months the monthly sales data for the corresponding sales representative is populated.

WITH calendar (reporting_month) AS (
    SELECT
    	TRUNC(MIN(oe.orders.order_date), 'MONTH')
    FROM
    	oe.orders
    UNION ALL
    SELECT
    	ADD_MONTHS(calendar.reporting_month, 1)
    FROM
    	calendar
    WHERE
    	calendar.reporting_month < (
    	SELECT
    		TRUNC(MAX(oe.orders.order_date), 'MONTH')
    	FROM
    		oe.orders
    )
),
sales_representative AS (
    SELECT
    	DISTINCT oe.orders.sales_rep_id
    FROM
    	oe.orders
    WHERE
    	oe.orders.sales_rep_id IS NOT NULL
),
monthly_sales_template AS (
	SELECT
		sales_representative.sales_rep_id,
		calendar.reporting_month
	FROM
		calendar
		CROSS JOIN
		sales_representative
),
monthly_sales_summary AS (
    SELECT
		oe.orders.sales_rep_id,
        TRUNC(oe.orders.order_date, 'MONTH') AS order_month,
		COUNT(DISTINCT oe.orders.sales_rep_id) AS order_count,
        SUM(oe.order_items.quantity) AS quantity_total,
        SUM(oe.order_items.quantity * oe.order_items.unit_price) AS sales_total
    FROM
        oe.orders
        INNER JOIN
        oe.order_items
            ON oe.orders.order_id = oe.order_items.order_id
        	AND oe.orders.sales_rep_id IS NOT NULL
    GROUP BY
		oe.orders.sales_rep_id,
        TRUNC(oe.orders.order_date, 'MONTH')
)
SELECT
	monthly_sales_template.sales_rep_id,
	monthly_sales_template.reporting_month,
    COALESCE(monthly_sales_summary.order_count, 0) AS order_count,
    COALESCE(monthly_sales_summary.quantity_total, 0) AS quantity_total,
    COALESCE(monthly_sales_summary.sales_total, 0) AS sales_total
FROM
	monthly_sales_template
	LEFT JOIN
	monthly_sales_summary
		ON monthly_sales_template.sales_rep_id = monthly_sales_summary.sales_rep_id
		AND monthly_sales_template.reporting_month = monthly_sales_summary.order_month
ORDER BY
    monthly_sales_template.sales_rep_id,
    monthly_sales_template.reporting_month;