Basic SQL - Learning MySQL - page 159

+-----------+-------------+----------+------------------------------------------+
| artist_id | artist_name | album_id | album_name |
+-----------+-------------+----------+------------------------------------------+
| 1 | New Order | 1 | Retro - John McCready FAN |
| 1 | New Order | 2 | Substance (Disc 2) |
| 1 | New Order | 3 | Retro - Miranda Sawyer POP |
| 1 | New Order | 4 | Retro - New Order / Bobby Gillespie LIVE |
| 1 | New Order | 5 | Power, Corruption & Lies |
| 1 | New Order | 6 | Substance 1987 (Disc 1) |
| 1 | New Order | 7 | Brotherhood |
+-----------+-------------+----------+------------------------------------------+
Once it has processed all the different artist_id values, it selects the colums you asked
for— artist_name and album_name —to display:
+---------------------------+------------------------------------------+
| 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 |
...
+---------------------------+------------------------------------------+
There are a few important issues you need to know about when using the basic INNER
JOIN syntax:
• It works only when two tables share a column with the same name that you can
use as the join condition; otherwise, you must use an alternative syntax described
in Chapter 7. Note that MySQL can't automatically determine the column you
want to use for the join, (even if there are columns with the same name in the two
tables), so you have to specify it explicitly.
• The result rows shown are those where the join column (or columns) match be-
tween the tables; rows from one table that don't have a match in the other table
are ignored. In the previous example, any artist who had no albums would be
ignored.
• With the exception of the join column or columns after the USING keyword, any
columns you specify must be unambiguous. For example, if you want to SELECT
the artist_name , you can use just artist_name because it exists only in the artist
table. However, if you want artist_id , then you need to specify it explicitly as
artist.artist_id or album.artist_id because both tables have a column of the
same name.
• Don't forget the USING clause. MySQL won't complain if you omit it, but the results
won't make sense because you'll get a Cartesian product . We discuss this further
in Chapter 7.