The SELECT Statement and Basic Querying Techniques | 159

• The column or columns following the
USING
clause must be surrounded by parentheses. 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 straightforward. 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 inset 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 book, 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 seethe 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

Download 4.24 Mb.

Share with your friends: