SQL Pro tips : AWS Athena SQL UNPIVOT : CROSS JOIN UNNEST
Tags: aws, database, bigdata, sql
Published: 2023-10-06 15:20:22

## 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.
We shall perform now the same operation with [AWS Athena](https://aws.amazon.com/athena/)
Feel free to check the [Oracle XE CROSS JOIN UNPIVOT tips](https://dev.to/hexfloor/sql-pro-tips-oracle-sql-cross-join-with-unpivot-57ff) or a [AWS documentation](https://docs.aws.amazon.com/athena/latest/ug/flattening-arrays.html).
I'm writing this guide to provide a simple step-by-step manual easy to understand.

## UNPIVOT
If you are new to `Athena` you may wish to go through the [quick start guide](https://docs.aws.amazon.com/athena/latest/ug/getting-started.html)

Let's start straight away with something trivial : 

```
select 'SUNDAY' as day0, 'MONDAY' as day1;

```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c83hjjfhk3fgq4ugka2g.png)

and use `array`

```
select array['SUNDAY', 'MONDAY'] as day;

```


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uns5px84qwkz9c6f7m9m.png)

now, let's flatten the `array` with `UNNEST` : 

```
select * from unnest(array['SUNDAY', 'MONDAY']) t(day);

```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bpqsviv3inon3jwn82ms.png)

and add another column into the output : 

```
with 
    in_data as (select * from unnest(array['SUNDAY', 'MONDAY'], array[0, 1]) t(day, day_order))
select * from in_data;


```


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/90ke2qf5g50sbo517v5u.png)

That was fast and straight to the point! Let's explore `CROSS JOIN`

## CROSS JOIN
In a nutshell [CROSS JOIN](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#cross_join)
operation is simple = it's just a Cartesian product of the two tables, take all the possible combinations from the values on the left with the values on the right.
Few examples : 

```
with 
    week_data as (select '2024W01' as week_ref),
    day_data as (select 'SUNDAY' as day_ref)
select * from week_data cross join day_data;

```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s95km774tsglwu53ty9g.png)

with `UNION ALL` :

```
with 
    week_data as (select '2024W01' as week_ref),
    day_data as (select 'SUNDAY' as day_ref 
    union all select 'MONDAY' as day_ref)
select * from week_data cross join day_data;

```


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h7km4bzv2xu8z50z415x.png)

and with `array` : 

```
with 
    week_data as (select '2024W01' as week_ref),
    day_data as (select array['SUNDAY','MONDAY'] as day_ref)
select * from week_data cross join day_data;
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l9q37izyu6xkp3cbxnm2.png)


let's flatten the array : 

```
with 
    week_data as (select '2024W01' as week_ref),
    day_data as (select * from unnest(array['SUNDAY','MONDAY']) r(day_ref))
select * from week_data cross join day_data;

```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x8fq34793vhp9jsmq9i5.png)

and add another one to show the full power of the `CROSS JOIN` : 

```
with 
    week_data as (select * from unnest(array['2024W01', '2024W02']) l(week_ref)),
    day_data as (select * from unnest(array['SUNDAY','MONDAY']) r(day_ref))
select * from week_data cross join day_data;

```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xp9f9hf2ppwou6ukiujl.png)

Finally we are ready to combine both `CROSS JOIN` and `UNNEST`

## CROSS JOIN UNNEST
The operation I'm going to perform is named `UNPIVOT` in `Oracle XE`, [see here](https://dev.to/hexfloor/sql-pro-tips-oracle-sql-cross-join-with-unpivot-57ff) at the same time `BigQuery` has both `UNPIVOT` and `UNNEST`, you may [check here](https://dev.to/hexfloor/sql-pro-tips-gcp-bigquery-sql-cross-join-with-unpivot-unnest-33lf)

Let's have some test data : 

```
with 
    in_data as (select '2024W01' as week_ref, 'SUNDAY' as day0, 0 as day_order0, 'MONDAY' as day1, 1 as day_order1)
select * from in_data;
```


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r1r46jrkeoa7z3vsjcw2.png)

Firstly, let's generate the result with `UNION ALL` : 

```
with 
    in_data as (select '2024W01' as week_ref, 'SUNDAY' as day0, 0 as day_order0, 'MONDAY' as day1, 1 as day_order1)
select week_ref, day0 as day, day_order0 as day_order from in_data
union all 
select week_ref, day1 as day, day_order1 as day_order from in_data;
```
The query above will scan the `in_data` as many times as many subqueries you have, in this example we have 2 subqueries.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ivld47ohilzuxln3qd19.png)

and finally the performance optimization with `CROSS JOIN UNNEST` : 

```
with 
    in_data as (select '2024W01' as week_ref, 'SUNDAY' as day0, 0 as day_order0, 'MONDAY' as day1, 1 as day_order1)
select week_ref, day, day_order
from in_data
cross join unnest(array[day0, day1], array[day_order0, day_order1]) t(day, day_order);
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w3asyxt5lr9cnpy5wql2.png)

Using `UNPIVOT` increases performance of your data analytics requests and saves the energy.
Enjoy !