Learning Mysql

become obvious that by using these relationships, we can answer more interesting queries. For example, it’d be useful to know what tracks makeup an album, what albums we own by each artist, or how long an album plays for. This section shows you how to answer these queries by joining two tables. We’ll return to this issue as part of a longer, more advanced discussion of joins in Chapter We use only one join syntax in this chapter. There are several more, and each gives you a different way to bring together data from two or more tables. The syntax we use here is the INNER JOIN, which hides some of the detail and is the easiest to learn. Consider an example, and then we’ll explain more about how it works:
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)
The output shows the artists and their albums. You can see for the first time how many albums we own by each artist and who made each one.
How does the INNER JOIN
work? The statement has two parts first, two table names separated by the INNER JOIN
keywords; second, the
USING
keyword that indicates which column (or columns) holds the relationship between the two tables. In our first example, the two tables to be joined are artist and album, expressed as artist INNER JOIN
album
(for the basic INNER JOIN, it doesn’t matter what order you list the tables in, and sousing album INNER JOIN artist would have the same effect. The
USING
clause in the example is USING (artist_id)
, which tells MySQL that the column that holds there- lationship between the tables is artist_id
; you should recall this from our design and our previous discussion in The Music Database in Chapter The data comes from the artist table:
mysql> SELECT * FROM artist;
+-----------+---------------------------+
| artist_id | artist_name |
+-----------+---------------------------+
| 1 | New Order |

Download 4.24 Mb.

Share with your friends: