Tutorials

SQL UNIONS

We saw that the purpose of joins is to append the columns selected from the matching rows of multiple tables to each output row, thus expanding the query results horizontally. With unions the columns stay the same and the query results are appended vertically by adding more rows.

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

SELECT a, b FROM table1
UNION
SELECT x, y FROM table2

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.

SELECT a, b, c FROM table1
UNION
SELECT x , y, NULL FROM table2

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 both occurrences to appear 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 each of the ex-army barracks that contains the pupils' dormitory 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.

SELECT h.house_name
    , 'Housemaster' as status
    , CONCAT(t.fname,' ',t.lname) as name 
FROM house h
    INNER JOIN teacher t ON h.house_master = t.teacherid
UNION
SELECT h.house_name
    , 'Pupil' as status
    , CONCAT(p.fname,' ',p.lname) as name
FROM pupil p
    INNER JOIN house h USING (houseID)
ORDER BY house_name, status

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       |
+------------+-------------+------------------+

Caroline, who is fifteen tomorrow, has asked for help with her party invitation mailing list. She cannot afford bottles of vodka for everyone in the school so she just wants to invite those in her house plus those with whom she shares lessons (same class and subjects)

SELECT 
  CONCAT(p1.fname,' ',p1.lname) as pupil
, CONCAT(p2.fname,' ',p2.lname) as invites
FROM pupil p1
INNER JOIN choice c1 ON p1.pupilid = c1.pupilid
INNER JOIN pupil p2 ON p1.pupilid <> p2.pupilid AND p1.classid = p2.classid
INNER JOIN choice c2 ON p2.pupilid = c2.pupilid AND c2.subjectid = c1.subjectid
WHERE p1.pupilid = 6
UNION
SELECT 
  CONCAT(p1.fname,' ',p1.lname) as pupil
, CONCAT(p2.fname,' ',p2.lname) as invites
FROM pupil p1 
INNER JOIN pupil p2 ON p1.houseid = p2.houseid AND p1.pupilid <> p2.pupilid
WHERE p1.pupilid = 6 

Results

+------------------+-----------------+
| pupil            | invites         |
+------------------+-----------------+
| Caroline Freeman | John Patterson  |
| Caroline Freeman | William Smith   |
| Caroline Freeman | Mary Sheldon    |
| Caroline Freeman | Allan Blair     |
| Caroline Freeman | Anna Hamilton   |
| Caroline Freeman | John Watson     |
| Caroline Freeman | John Williams   |
| Caroline Freeman | Mary Whitehouse |
+------------------+-----------------+

Next: SUB-QUERIES