Databases Reference
In-Depth Information
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 in set (0.00 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 a way 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 in set (0.00 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 ad-
vantage 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.
