+---------------------------+
4 rows inset 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 inset sec)
The Left and Right JoinsThe joins we’ve discussed so far output only rows that match between tables. For example, 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 participating 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 FROMShare with your friends: 
