Sql - UNION and UNION ALL
Code Life
By Pierre Marsaa
Stacking two results
Where do the trainers live? One query answers that:
↗️
Loading editor...
↶
↷
And where are the gyms? Another query, another list:
↗️
Loading editor...
↶
↷
Now you want one list of every city of the league. A join would put the two tables side by side, but here you have two lists of the same kind of thing, and you want them on top of each other.
That is what UNION does: write the two complete SELECTs, one after the other, with UNION in between:
↗️
Loading editor...
↶
↷
The two lists had 4 + 5 = 9 names, but UNION removes the duplicates: 4 distinct cities remain — Moonlight Hollow included, even though no trainer lives there.
The first SELECT names the columns
The result column is called city because the first SELECT says AS city; the names of the second SELECT are ignored. ORDER BY is written once, at the very end, and sorts the whole stack.
Verify