SQL Pro tips : Oracle SQL CROSS JOIN with UNPIVOT

Introduction

TL;DR : Why to use UNPIVOT over UNION ALL ? Performance, UNION ALL scans all the data for each subquery, with UNPIVOT the scan happens only once.
Disclaimer : in the example below WEEK_NUMBER is not a suitable identifier for the varchar type, the better name is WEEK_REF and the variables should better be prefixed with year, like 2024W01

Setup

Feel free to check the Oracle XE Quick Start
I will be using docker enabled Oracle XE 21.3.0 with DBeaver as a client.

UNPIVOT

Before starting, I'm inviting you to check a wonderful article from Oracle regarding PIVOTand UNPIVOT. If this article does exist, why to write another one? The answer is simple : because this article is a step-by-step guide which will help you to understand over copy-paste.
While writing this article I have found another good article about unpivot, check it out !

What is the business problem we are trying to solve : as a result of an aggregation over a big chunk of data we have a dataset composed with rows with multiple columns that we would like to stack one below another, probably with the common prefix for each chunk of the row stacked in the new representation.
Let's have an example :

SELECT 
    'SUNDAY' AS DAY_0,
    'MONDAY' AS DAY_1
FROM DUAL;

Simple stacking with UNION ALL :

WITH 
    DAY_DATA AS 
        (SELECT 
            'SUNDAY' AS DAY_0,
            'MONDAY' AS DAY_1
        FROM DUAL)
SELECT DAY_0 AS DAY_NAME FROM DAY_DATA
UNION ALL 
SELECT DAY_1 AS DAY_NAME FROM DAY_DATA;

And the same result with UNPIVOT :

SELECT DAY_NAME FROM (
    (SELECT 
        'SUNDAY' AS DAY_0,
        'MONDAY' AS DAY_1
    FROM DUAL)
    UNPIVOT (
        DAY_NAME FOR DAY_COLUMN IN (DAY_0, DAY_1)
    )
)

Great ! Let's move on and add two more columns that we would like to stack :

SELECT 
    'SUNDAY' AS DAY_0,
    0 AS DAY_ORDER_0,
    'MONDAY' AS DAY_1,
    1 AS DAY_ORDER_1
FROM DUAL

Stacking values from the columns DAY_0 and DAY_1 into the column DAY_NAME and values from the columns DAY_ORDER_0 and DAY_ORDER_1 into the column DAY_ORDER with UNION ALL :

WITH 
    DAY_DATA AS 
        (SELECT 
            'SUNDAY' AS DAY_0,
            1 AS DAY_ORDER_0,
            'MONDAY' AS DAY_1,
            1 AS DAY_ORDER_1
        FROM DUAL)
SELECT DAY_0 AS DAY_NAME, DAY_ORDER_0 AS DAY_ORDER FROM DAY_DATA
UNION ALL 
SELECT DAY_1 AS DAY_NAME, DAY_ORDER_1 AS DAY_ORDER FROM DAY_DATA;

And the same result with UNPIVOT

SELECT * FROM (
    (SELECT 
        'SUNDAY' AS DAY_0,
        1 AS DAY_ORDER_0,
        'MONDAY' AS DAY_1,
        1 AS DAY_ORDER_1
    FROM DUAL)
    UNPIVOT (
        (DAY_NAME, DAY_ORDER) 
        FOR DAY_COLUMN IN (
            (DAY_0, DAY_ORDER_0) AS 'DAY_0',
            (DAY_1, DAY_ORDER_1) AS 'DAY_1'
        )
    )
)

And when we have a common classifier :

SELECT 
    WEEK_NUMBER,
    DAY_NAME,
    DAY_ORDER
FROM (
    (SELECT 
        'W01' AS WEEK_NUMBER,
        'SUNDAY' AS DAY_0,
        1 AS DAY_ORDER_0,
        'MONDAY' AS DAY_1,
        1 AS DAY_ORDER_1
    FROM DUAL)
    UNPIVOT (
        (DAY_NAME, DAY_ORDER) 
        FOR DAY_COLUMN IN (
            (DAY_0, DAY_ORDER_0) AS 'DAY_0',
            (DAY_1, DAY_ORDER_1) AS 'DAY_1'
        )
    )
)

That's it !

CROSS JOIN

Example above uses implicit CROSS JOIN under the hood, you may check an Oracle article about CROSS JOIN
Firstly let's have a simple example of CROSS JOIN :

WITH WEEK_DATA AS (
    SELECT 
        'W01' AS WEEK_NUMBER 
    FROM DUAL),
    DAY_DATA AS (
        SELECT 'SUNDAY' AS DAY_NAME FROM DUAL
    )
SELECT * FROM WEEK_DATA CROSS JOIN DAY_DATA;

Adding another row to the DAY_DATA:

WITH WEEK_DATA AS (
    SELECT 
        'W01' AS WEEK_NUMBER 
    FROM DUAL),
    DAY_DATA AS (
        SELECT 'SUNDAY' AS DAY_NAME FROM DUAL
        UNION ALL
        SELECT 'MONDAY' AS DAY_NAME FROM DUAL
    )
SELECT * FROM WEEK_DATA CROSS JOIN DAY_DATA;

Adding another row to the WEEK_DATA :

WITH WEEK_DATA AS (
        SELECT 'W01' AS WEEK_NUMBER FROM DUAL
        UNION ALL 
        SELECT 'W02' AS WEEK_NUMBER FROM DUAL
    ),
    DAY_DATA AS (
        SELECT 'SUNDAY' AS DAY_NAME FROM DUAL
        UNION ALL
        SELECT 'MONDAY' AS DAY_NAME FROM DUAL
    )
SELECT * FROM WEEK_DATA CROSS JOIN DAY_DATA;

CROSS JOIN with UNPIVOT

Let's combine both CROSS JOIN and UNPIVOT :

WITH WEEK_DATA AS (
        SELECT 'W01' AS WEEK_NUMBER FROM DUAL
    ),
    DAY_DATA AS (
        SELECT 
            DAY_NAME,
            DAY_ORDER
        FROM (
            (SELECT 
                'SUNDAY' AS DAY_0,
                1 AS DAY_ORDER_0,
                'MONDAY' AS DAY_1,
                1 AS DAY_ORDER_1
            FROM DUAL)
            UNPIVOT (
                (DAY_NAME, DAY_ORDER) 
                FOR DAY_COLUMN IN (
                    (DAY_0, DAY_ORDER_0) AS 'DAY_0',
                    (DAY_1, DAY_ORDER_1) AS 'DAY_1'
                )
            )
        )
    )
SELECT * FROM WEEK_DATA CROSS JOIN DAY_DATA;

Now you know how to combine both CROSS JOIN and UNPIVOT, the concepts explained above are semantically the same for the Big Data solutions hosted in different cloud providers, I will show few examples in the following articles. Stay tuned !