Databases Reference
In-Depth Information
| Temptation |
| True Faith |
| The Perfect Kiss |
| Ceremony |
| New Blues |
| Intruder |
| In A Silent Way |
| Bizarre Love Triangle |
| Crystal |
+-----------------------+
10 rows in set (0.09 sec)
The first query uses
ORDER
BY
with the
ASC
(ascending) modifier and a
LIMIT 5
clause to
find the first five tracks played. The second query uses
ORDER BY
with the
DESC
(de-
scending) modifier and a
LIMIT 5
clause to find the last five tracks played. The
UNION
combines the result sets.
The
UNION
operator has several limitations:
• The output is labeled with the names of the columns or expressions from the first
query. Use column aliases to change this behavior.
• The queries should output the same number of columns. If you try using different
numbers of columns, MySQL will report an error.
• All matching columns should have the same type. So, for example, if the first col-
umn output from the first query is a date, the first column output from any other
query must be a date.
• The results returned are unique, as if you'd applied a
DISTINCT
to the overall result
set. To see this in action, let's add a new row for the track “Fine Time” to the
played
table. This has
artist_id
1,
album_id
3, and
track_id
0:
mysql>
INSERT INTO played SET
-> artist_id = 1,
-> album_id = 3,
-> track_id = 0,
-> played='2006-08-14 10:27:03';
Query OK, 1 row affected (0.02 sec)
We've used the more verbose
INSERT
format to clarify what we're inserting.
Now, if you run the previous
SELECT
query again, you'll see 9 rows instead of 10,
since “Fine Time” appears twice in the first 5 tracks placed, but the implicit
DIS
TINCT
operation means it's shown only once:
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 |
