Learn SQL: Stacking results with UNION
Everything on one list
The last evening. Griet wants one press release with everything in it: the towns the race passed through and the leaders of the classifications. All from different queries, but on one list.
Two results on top of each other
UNION puts the results of two queries below each other. Where a JOIN puts columns side by side, UNION stacks rows.
UNION also removes the duplicates straight away. Lennik and Halle are both a start and a finish, but appear only once in the list.
SELECT start AS town FROM stages UNION SELECT finish FROM stages| town |
|---|
| Bever |
| Galmaarden |
| Gooik |
| Halle |
| Herne |
| Lennik |
| Pepingen |
UNION ALL and the rules of the game
UNION ALL does keep the duplicates, and is faster because of it. Three rules always apply: both queries have the same number of columns, the names come from the first query, and an ORDER BY is only allowed at the very end.
With a fixed text as a column you can see where each row comes from.
SELECT start AS town, 'start' AS role FROM stages UNION ALL SELECT finish, 'finish' FROM stages ORDER BY town| town | role |
|---|---|
| Bever | start |
| Galmaarden | finish |
| Gooik | start |
| Halle | start |
| Halle | finish |
| Herne | start |
| Lennik | start |
| Lennik | finish |
Try it yourself
Show every town where a stage starts or finishes, with how often it appears in total (starts and finishes together).
Ready for more?
The full lesson has 5 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson