Lets 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)Share with your friends: 
