-> artist INNER JOIN album USING (artist_id)

Advanced Joins
So far in the book, we’ve used the INNER JOIN
clause to bring together rows from two or more tables. We’ll explain the inner join in more detail in this section, contrasting it with the other join types we explain the union, left and right joins, and natural joins.
At the conclusion of this section, you’ll be able to answer difficult information needs and be familiar with the correct choice of join for the task.
The Inner Join
The
INNER
JOIN
clause matches rows between two tables based on the criteria you provide in the
USING
clause. For example, you’re very familiar now with an inner join of the artist and album tables:
mysql> SELECT artist_name, album_name FROM
-> artist INNER JOIN album USING (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)
Let’s review the key features of an INNER JOIN Two tables (or results of a previous join) are listed on either side of the
INNER
JOIN
keyphrase.
• The
USING
clause defines one or more columns that are in both tables or results,
and used to join or match rows Rows that don’t match aren’t returned. For example, if you have a row in the artist table that doesn’t have any matching albums in the album table, it won’t be included in the output.
You can actually write inner-join queries with the
WHERE
clause without using the
INNER
JOIN
keyphrase. Here’s a rewritten version of the previous query that produces the same result:

Download 4.24 Mb.

Share with your friends: