Learning MySQL

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 ...