-> track INNER JOIN played USING (artist_id, album_id, track_id)

+-----------------------+
| Fine Time |
| Temptation |
| True Faith |
| The Perfect Kiss |
| New Blues |
| Intruder |
| In A Silent Way |
| Bizarre Love Triangle |
| Crystal |
+-----------------------+
9 rows inset sec)
If you want to show any duplicates, replace
UNION
with UNION ALL
:
mysql> (SELECT track_name FROM
-> track INNER JOIN played USING (artist_id, album_id, track_id)
-> ORDER BY played ASC LIMIT 5)
-> UNION ALL
-> (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 |
| Temptation |
| Fine Time |
| True Faith |
| The Perfect Kiss |
| New Blues |
| Intruder |
| In A Silent Way |
| Bizarre Love Triangle |
| Crystal |
+-----------------------+
10 rows inset sec)
Here, Fine Time appears twice If you want to apply
LIMIT
or ORDER BY
to an individual query that is part of a
UNION
statement, enclose that query in parentheses (as shown in the previous example. It’s useful to use parentheses anyway to keep the query easy to understand.
The
UNION
operation simply concatenates the results of the component queries with no attention to order, so there’s not much point in using ORDER BY
within one of the subqueries. The only time that it makes sense to order a subquery in a
UNION
operation is when you want to select a subset of results. In our example, we’ve ordered the tracks by the time they were played, and then selected only the first five (in the first subquery) and the last five (in the second subquery).
For efficiency, MySQL will actually ignore an ORDER BY
clause within a subquery if it’s used without LIMIT. Let’s look at some examples to see exactly how this works.

Download 4.24 Mb.

Share with your friends: