Learning Mysql

album al INNER JOIN artist ar
Again, we prefer the
AS
style, as it’s clearer to anyone looking at your queries than the alternative. The restrictions on table-alias-name characters and lengths are the same as column aliases, and our recommendations on choosing them are the same, too.
As discussed in the introduction to this section, table aliases allow you to write queries that you can’t otherwise easily express. Consider an example suppose you want to know whether two or more artists have released an album of the same name and, if so,
what the identifiers for those artists are. Let’s think about the basic requirement you want to know if two albums have the same name. To do this, you might try a query like this:
mysql> SELECT * FROM album WHERE album_name = album_name;
But that doesn’t make sense an album has the same name as itself, and so it just produces all albums as output 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 inset sec)
What you really want is to know if two different albums from the album table have the same name. But how can you do that in a single query The answer is to give the table two different aliases you then check if one row in the first aliased table matches a row in the second:
mysql> SELECT a1.artist_id, a2.album_id

Download 4.24 Mb.

Share with your friends: