Databases Reference
In-Depth Information
+---------------------------+
4 rows in set (0.01 sec)
The
UNION
operation is somewhat unwieldy, and there are generally alternative ways of
getting the same result. For example, the previous query could have been written more
simply as:
mysql>
SELECT artist_name FROM artist WHERE
-> artist_id < 3 OR artist_id > 5
-> ORDER BY artist_name LIMIT 4;
+---------------------------+
| artist_name |
+---------------------------+
| Kylie Minogue |
| New Order |
| Nick Cave & The Bad Seeds |
+---------------------------+
3 rows in set (0.00 sec)
The Left and Right Joins
The joins we've discussed so far output only rows that match between tables. For ex-
ample, when you join the
track
and
played
tables, you see only the tracks that have
been played. Therefore, rows for tracks that haven't been played are ignored and—if
they existed—would play data for tracks that don't exist. This makes sense in many
cases, but it isn't the only way to join data. This section explains other options you have.
Suppose you did want a comprehensive list of all albums and the number of times
you've played tracks from them. Unlike the example earlier in this chapter, included
in the list you want to see a zero next to albums that haven't been played. You can do
this with a
left join
, a different type of join that's driven by one of the two tables par-
ticipating in the join. A left join works like this: each row in the left table—the one
that's doing the driving—is processed and output, with the matching data from the
second table if it exists and
NULL
values if there is no matching data in the second table.
We'll show you how to write this type of query later in this section, but we'll start with
a simpler example.
Here's a simple
LEFT JOIN
example. You want to list all tracks, and next to each track
you want to show when it was played. If a track has been never been played, you want
to see that. If it's been played many times, you want to see that too. Here's the query:
mysql>
SELECT track_name, played FROM
-> track LEFT JOIN played USING (artist_id, album_id, track_id)
-> ORDER BY played DESC;
+--------------------------+---------------------+
| track_name | played |
+--------------------------+---------------------+
| New Blues | 2006-08-15 14:33:57 |
| Intruder | 2006-08-15 14:26:12 |
| In A Silent Way | 2006-08-15 14:00:03 |
| Bizarre Love Triangle | 2006-08-14 10:54:02 |
