Databases Reference
In-Depth Information
Advanced Joins
So far in the topic, 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 pro-
vide 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 in set (0.00 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:
