Databases Reference
In-Depth Information
| 2 | Nick Cave & The Bad Seeds |
| 3 | Miles Davis |
| 4 | The Rolling Stones |
| 5 | The Stone Roses |
| 6 | Kylie Minogue |
+-----------+---------------------------+
6 rows in set (0.01 sec)
and the
album
table:
mysql>
SELECT * FROM album;
+-----------+----------+------------------------------------------+
| artist_id | album_id | album_name |
+-----------+----------+------------------------------------------+
| 2 | 1 | Let Love In |
| 1 | 1 | Retro - John McCready FAN |
| 1 | 2 | Substance (Disc 2) |
| 1 | 3 | Retro - Miranda Sawyer POP |
| 1 | 4 | Retro - New Order / Bobby Gillespie LIVE |
| 3 | 1 | Live Around The World |
| 3 | 2 | In A Silent Way |
| 1 | 5 | Power, Corruption & Lies |
| 4 | 1 | Exile On Main Street |
| 1 | 6 | Substance 1987 (Disc 1) |
| 5 | 1 | Second Coming |
| 6 | 1 | Light Years |
| 1 | 7 | Brotherhood |
+-----------+----------+------------------------------------------+
13 rows in set (0.00 sec)
In response to our query, MySQL finds the
artist_name
and
album_name
value pairs that
have the same
artist_id
values. For each
artist_id
in the
artist
table (let's use 1 as
an example):
+-----------+---------------------------+
| artist_id | artist_name |
+-----------+---------------------------+
| 1 | New Order |
+-----------+---------------------------+
the server finds all the entries in the
album
table that have this value of
artist_id
:
+-----------+----------+------------------------------------------+
| artist_id | album_id | album_name |
+-----------+----------+------------------------------------------+
| 1 | 1 | Retro - John McCready FAN |
| 1 | 2 | Substance (Disc 2) |
| 1 | 3 | Retro - Miranda Sawyer POP |
| 1 | 4 | Retro - New Order / Bobby Gillespie LIVE |
| 1 | 5 | Power, Corruption & Lies |
| 1 | 6 | Substance 1987 (Disc 1) |
| 1 | 7 | Brotherhood |
+-----------+----------+------------------------------------------+
It can then form a new temporary table from these two sets:
