Learning Mysql

mysql> SELECT artist_name, album_name FROM artist, album
-> WHERE artist.artist_id = album.artist_id;
+---------------------------+------------------------------------------+
| artist_name | album_name |
+---------------------------+------------------------------------------+
| New Order | Retro - John McCready FAN |
| New Order | Substance (Disc 2) |
| New Order | Retro - Miranda Sawyer POP |
| New Order | Retro - New Order / Bobby Gillespie LIVE |
| New Order | Power, Corruption & Lies |
| New Order | Substance 1987 (Disc 1) |
| New Order | Brotherhood |
| Nick Cave & The Bad Seeds | Let Love In |
| Miles Davis | Live Around The World |
| Miles Davis | In A Silent Way |
| The Rolling Stones | Exile On Main Street |
| The Stone Roses | Second Coming |
| Kylie Minogue | Light Years |
+---------------------------+------------------------------------------+
13 rows inset sec)
You can see that we’ve spelled out the inner join we’re selecting from the artist and album tables the rows where the identifiers match between the tables.
You can modify the INNER JOIN
syntax to express the join criteria in away that’s similar to using a
WHERE
clause. This is useful if the names of the identifiers don’t match between the tables. Here’s the previous query, rewritten in this style:
mysql> SELECT artist_name, album_name FROM
-> artist INNER JOIN album ON artist.artist_id = album.artist_id;
+---------------------------+------------------------------------------+
| artist_name | album_name |
+---------------------------+------------------------------------------+
| New Order | Retro - John McCready FAN |
| New Order | Substance (Disc 2) |
| New Order | Retro - Miranda Sawyer POP |
| New Order | Retro - New Order / Bobby Gillespie LIVE |
| New Order | Power, Corruption & Lies |
| New Order | Substance 1987 (Disc 1) |
| New Order | Brotherhood |
| Nick Cave & The Bad Seeds | Let Love In |
| Miles Davis | Live Around The World |
| Miles Davis | In A Silent Way |
| The Rolling Stones | Exile On Main Street |
| The Stone Roses | Second Coming |
| Kylie Minogue | Light Years |
+---------------------------+------------------------------------------+
13 rows inset sec)
You can see that the
ON
clause replaces the
USING
clause, and that the columns that follow are fully specified to include the table and column names. There’s no real advantage or disadvantage in using
ON
or a
WHERE
clause; it’s just a matter of taste. Typically,
you’ll find most SQL professionals use the
WHERE
clause in preference to INNER JOIN
,
most likely because it’s the technique they learned first.

Download 4.24 Mb.

Share with your friends: