Advanced Querying - Learning MySQL - page 242

+-----------------------+
| Fine Time |
| Temptation |
| True Faith |
| The Perfect Kiss |
| New Blues |
| Intruder |
| In A Silent Way |
| Bizarre Love Triangle |
| Crystal |
+-----------------------+
9 rows in set (0.01 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 in set (0.00 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 ex-
ample). 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.