Union All

Jun 27, 2021 sql

## Use Case
Suppose you have a table like this:

| name      | maths| science | english |
| --------- | ---- | ------- | ------- |
| Robbo     | 90   | 82      | 45      | 
| Trent     | 30   | 71      | 72      |

And you want to transpose it and make it look like

| name      | subject| marks |
| --------- | ------ | ----- |
| Robbo     | maths  | 90    | 
| Robbo     | science| 82    | 
| Robbo     | english| 45    | 
| Trent     | maths  | 30    | 
| Trent     | science| 71    | 
| Trent     | english| 72    |

## Thinking process
I need to rotate it, but how? Looking at the result, the first and the fourth row of the result is simply
```
select name, "maths" as subject, maths as marks from students;
```

Similarly second and the fifth row is
```
select name, "science" as subject, science as marks from students;
```

and the third and the sixth row is
```
select name, "english" as subject, english as marks from students;
```

and now I need to union them all to get the result
```
select name, "maths" as subject, maths as marks from students
union all
select name, "science" as subject, science as marks from students
union all
select name, "english" as subject, english as marks from students;
```

| name      | subject| marks |
| --------- | ------ | ----- |
| Robbo     | maths  | 90    | 
| Trent     | maths  | 30    | 
| Robbo     | science| 82    | 
| Trent     | science| 71    | 
| Robbo     | english| 45    | 
| Trent     | english| 72    |

That should do it!

That 45 for Robbo for shouldn't surprise you, I need a translator whenever I listen to him. 
Thankfully I don't need one for his on-field antics. Here is Trent and Robbo setting up Mo.
https://www.youtube.com/watch?v=C_l_1HgOaIM

## SideNote:
The above is also called unpivot and it exists as a function in SQL Server. Not in MySQL. I wasted so much time on that.

Also - if you have a venn diagram of A and B circles.
Union all is A and B both shaded with the intersection picked up once.
Union is A and B both shaded with the intersection picked up twice.

Thanks for reading along. I'm @anirudhonezero on twitter.