Basic SQL - Learning MySQL - page 160

• The column or columns following the USING clause must be surrounded by paren-
theses. If you want to join on more than one column, separate the column names
with a comma. We'll show you an example in a moment.
If you remember these rules, you'll find joins with INNER JOIN are reasonably straight-
forward. Let's now consider a few more examples that illustrate these ideas.
Suppose you want to list the track names for all your albums. Examining the album and
track tables, you identify that you would have to join two columns, artist_id and
album_id . Let's try the join operation:
mysql> SELECT album_name, track_name FROM album INNER JOIN track
-> USING (artist_id, album_id) LIMIT 15;
+---------------------------+----------------------------+
| album_name | track_name |
+---------------------------+----------------------------+
| Let Love In | Do You Love Me? |
| Let Love In | Nobody's Baby Now |
| Let Love In | Loverman |
| Let Love In | Jangling Jack |
| Let Love In | Red Right Hand |
| Let Love In | I Let Love In |
| Let Love In | Thirsty Dog |
| Let Love In | Ain't Gonna Rain Anymore |
| Let Love In | Lay Me Low |
| Let Love In | Do You Love Me? (Part Two) |
| Retro - John McCready FAN | Elegia |
| Retro - John McCready FAN | In A Lonely Place |
| Retro - John McCready FAN | Procession |
| Retro - John McCready FAN | Your Silent Face |
| Retro - John McCready FAN | Sunrise |
+---------------------------+----------------------------+
15 rows in set (0.00 sec)
We've specified the two join columns in the USING clause separated by commas as USING
(artist_id, album_id) . The results show the tracks for the album Let Love In , and the
first few from Retro - John McReady FAN . To fit the results into the topic, we've limited
the output to 15 rows, using the LIMIT clause we discussed earlier in “The LIMIT
Clause.”
We can improve our previous example by adding an ORDER BY clause. It makes sense
that we'd want to see the albums in alphabetical order, with the tracks shown in the
order they occur on the album, so we could modify our previous query to be:
mysql> SELECT album_name, track_name FROM album INNER JOIN track
-> USING (artist_id, album_id)
-> ORDER BY album_name, track_id LIMIT 15;
+----------------------+-----------------------+
| album_name | track_name |
+----------------------+-----------------------+
| Brotherhood | State of the Nation |
| Brotherhood | Every Little Counts |
| Brotherhood | Angel Dust |