Advanced Querying - Learning MySQL - page 240

Let's look at an example. If you wanted to output all of the text in the music database,
you could do this with a UNION statement. It's a contrived example, but you might want
to do this just to list all of the text fragments, rather than to meaningfully present the
relationships between the data. There's text in the artist_name , album_name , and
track_name columns in the artist , album , and track tables, respectively. Here's how to
display it:
mysql> SELECT artist_name FROM artist
-> UNION
-> SELECT album_name FROM album
-> UNION
-> SELECT track_name FROM track;
+------------------------------------------+
| artist_name |
+------------------------------------------+
| New Order |
| Nick Cave & The Bad Seeds |
| Miles Davis |
| The Rolling Stones |
| The Stone Roses |
| Kylie Minogue |
| Let Love In |
| Retro - John McCready FAN |
| Substance (Disc 2) |
| Retro - Miranda Sawyer POP |
| Retro - New Order / Bobby Gillespie LIVE |
| Live Around The World |
| In A Silent Way |
| Power, Corruption & Lies |
| Exile On Main Street |
| Substance 1987 (Disc 1) |
| Second Coming |
| Light Years |
| Brotherhood |
| Do You Love Me? |
...
We've only shown the first 20 of 153 rows. The UNION statement outputs all results from
all queries together, under a heading appropriate to the first query.
A slightly less contrived example is to create a list of the first five and last five tracks
you've played. You can do this easily with the UNION operator:
mysql> (SELECT track_name FROM
-> track INNER JOIN played USING (artist_id, album_id, track_id)
-> ORDER BY played ASC LIMIT 5)
-> UNION
-> (SELECT track_name FROM
-> track INNER JOIN played USING (artist_id, album_id, track_id)
-> ORDER BY played DESC LIMIT 5);
+-----------------------+
| track_name |
+-----------------------+
| Fine Time |