Learning Mysql

+-----------+-------------+----------+------------------------------------------+
| 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 area 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 between 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.

Download 4.24 Mb.

Share with your friends: