SQL Pro tips : CROSS JOIN UNPIVOT summary for beginners
Tags: sql, database, bigdata, beginners
Published: 2023-11-22 21:57:04

## 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.
I have written few technical guides on how to use `CROSS JOIN` and `UNPIVOT` in different dialects of SQL, you may check here : 

- [Oracle XE](https://dev.to/hexfloor/sql-pro-tips-oracle-sql-cross-join-with-unpivot-57ff)
- [BigQuery](https://dev.to/hexfloor/sql-pro-tips-gcp-bigquery-sql-cross-join-with-unpivot-unnest-33lf)
- [Athena](https://dev.to/hexfloor/sql-pro-tips-aws-athena-sql-unpivot-cross-join-unnest-444e)

However I wish to write even simpler guide using only cell's in a spreadsheet, I have used [Google Spreasheets](https://docs.google.com/spreadsheets)
The motivation behind is pretty trivial : numerous views for the articles and not so many likes, consequently the subject is not easy to understand and to fix it I will present the problem in as simple way as possible.

There will be 0 code in this guide.
As [Stephen Hawking](https://en.wikipedia.org/wiki/Stephen_Hawking) said : 

> Someone told me that each equation I included in the book would halve the sales. I therefore resolved not to have any equations at all. In the end, however, I did put in one equation, Einstein's famous equation, E = mc squared. I hope that this will not scare off half of my potential readers.

This guide is definitely not a masterpiece as [A Brief History of Time](https://en.wikipedia.org/wiki/A_Brief_History_of_Time)
hence there will be no equations, no formulae, no code.
Only cells of few colors, I will be using [orange and blue](https://priceonomics.com/why-every-movie-looks-sort-of-orange-and-blue/). And few shades of gray.

## UNPIVOT
Let's start with a simple example.

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

Hereby I wish to stockpile columns A, C into column A and columns B, D into column B, consequently the mapping is: 
A1 -> A1; B1 -> B1; C1 -> A2; D1 -> B2;

and the result : 

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

Now let's add another row into the dataset: 

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

The mapping is the following
A1 -> A1; B1 -> B1; C1 -> A2; D1 -> B2;
A2 -> A3; B2 -> B3; C2 -> A4; D2 -> B4;


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

Great! Let's see how to visualize the `CROSS JOIN`.

## CROSS JOIN
`CROSS JOIN` is pretty simple, take everything on the left and produce a pair with everything on the right.

Let's color just one cell into orange and one into blue

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ce4ng1udglvs1la3w29f.png)
`CROSS JOIN` is just trivial : 
AxB -> (A1,B1)

Let's add another cell of each color:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o5styga5tp3mi15bth6y.png)
AxB -> (A1,B1);(A1,B2);(A2,B1);(A2,B2);

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ch18z9gfkwr0bjdf1sss.png)
Now we are ready for the final touch.

## CROSS JOIN UNPIVOT
Now let's combine both:

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

I wish to `CROSS JOIN` columns A,B with the result of `UNPIVOT` of the columns (C,E) and (D,F):

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

Once the logic itself is clear the implementation will be clear as well, hence if you need to understand the concept I'm inviting you to read it through few times and to get the idea of the both operations used to manipulate the data first separately and then in combination.

Enjoy and stay tuned!