UNION

We saw that joins had the effect of appending columns, selected from the tables, to each row, thus expanding the query horizontally. With unions the columns stay the same and the query results are expanded vertically by adding more rows.

To create a union we take a SELECT query, add "UNION" then add another SELECT query. E.g.

As stated earlier, the columns stay the same so we cannot select a, b, c in one part and then select only x, y in another. However, we can do this to maintain the number of columns.

UNION ALL

By default, a union returns DISTINCT rows. This means that in our first example, if a, b contains "John", "Doe" and x, y also contains "John", "Doe" then it would appear only once in the results. If we want it to appear twice we use UNION ALL.

ORDER BY

The results will be sorted when all the results are in. The ORDER BY clause goes at the end of the whole query and applies to the whole result set.

Back to our school

"House" turns out to be a rather grand name for the ex-army barracks that contain the pupils' dormitories and the house-master's sleeping quarters. From a Health and Safety perspective, these dilapidated wooden structures are an accident waiting to happen. Good thing none of the pupils smoke cigarettes in their dorms. They have "No Smoking" signs to prevent that.

Anyway, I tactfully suggested that, as we had the data available, we could produce a register of each house as check lists to make sure all inhabitants were safely evacuated in the event of fire. These lists would need to include house-master and pupils for each house.

Results

+------------+-------------+------------------+
| house_name | status      | name             |
+------------+-------------+------------------+
| Cowdrey    | Housemaster | Robert Bingley   |
| Cowdrey    | Pupil       | Henry Irving     |
| Cowdrey    | Pupil       | Mary Blake       |
| Cowdrey    | Pupil       | Emma Watson      |
| Cowdrey    | Pupil       | Anthony Bell     |
| Cowdrey    | Pupil       | William Smith    |
| Cowdrey    | Pupil       | Margaret Norton  |
| Grace      | Housemaster | Charles Hadleigh |
| Grace      | Pupil       | Mary Whitehouse  |
| Grace      | Pupil       | Anna Hamilton    |
| Grace      | Pupil       | Allan Blair      |
| Grace      | Pupil       | Caroline Freeman |
| Grace      | Pupil       | John Williams    |
| Grace      | Pupil       | John Watson      |
| Jardine    | Housemaster | Pauline Fforbes  |
| Jardine    | Pupil       | John Tully       |
| Jardine    | Pupil       | Mary Sheldon     |
| Jardine    | Pupil       | Peter Appleby    |
| Jardine    | Pupil       | John Patterson   |
| Jardine    | Pupil       | Anne Bailey      |
| Jardine    | Pupil       | Michael Grove    |
| Laker      | Housemaster | Frank Morrisson  |
| Laker      | Pupil       | David Powell     |
| Laker      | Pupil       | Wayne Jones      |
| Laker      | Pupil       | Peter Adamson    |
| Laker      | Pupil       | Jane Morrison    |
| Laker      | Pupil       | Gearge Wilson    |
| Laker      | Pupil       | Adam Simms       |
+------------+-------------+------------------+