-> FROM artist INNER JOIN album USING (artist_id)

The MySQL function
CONCAT( )
concatenates together the strings that are parameters
—in this case, the artist_name
, a constant string recorded, and the album_name to give output such as New Order recorded Brotherhood. We’ve added an alias to the function,
AS recording, so that we can refer to it easily as recording throughout the query. You can see that we do this in the ORDER BY
clause, where we ask MySQL to sort the output by ascending recording value. This is much better than the unaliased alternative, which requires you to write out the
CONCAT( function again:
mysql> SELECT CONCAT(artist_name, " recorded ", album_name)
-> FROM artist INNER JOIN album USING (artist_id)
-> ORDER BY CONCAT(artist_name, " recorded ", album_name);
+-------------------------------------------------------------+
| recording |
+-------------------------------------------------------------+
| Kylie Minogue recorded Light Years |
| Miles Davis recorded In A Silent Way |
| Miles Davis recorded Live Around The World |
| New Order recorded Brotherhood |
| New Order recorded Power, Corruption & Lies |
| New Order recorded Retro - John McCready FAN |
| New Order recorded Retro - Miranda Sawyer POP |
| New Order recorded Retro - New Order / Bobby Gillespie LIVE |
| New Order recorded Substance (Disc 2) |
| New Order recorded Substance 1987 (Disc 1) |
| Nick Cave & The Bad Seeds recorded Let Love In |
| The Rolling Stones recorded Exile On Main Street |
| The Stone Roses recorded Second Coming |
+-------------------------------------------------------------+
13 rows inset sec)
The alternative is unwieldy, and worse, you risk mistyping some part of the ORDER BY
clause and getting a result different from what you expect. (Note that we’ve used as recording on the first line so that the displayed column has the label recording
.)
There are restrictions on where you can use column aliases. You can’t use them in a
WHERE
clause, or in the
USING
and
ON
clauses that we discuss later in this chapter. This means you can’t write a query such as:
mysql> SELECT artist_name AS a FROM artist WHERE a = "New Order";
ERROR 1054 (S Unknown column 'a' in 'where clause'
You can’t do this because MySQL doesn’t always know the column values before it executes the
WHERE
clause. However, you can use column aliases in the ORDER BY
clause,
and in the GROUP BY
and
HAVING
clauses discussed later in this chapter.
The
AS
keyword is optional. Because of this, the following two queries are equivalent:
mysql> SELECT artist_id AS id FROM artist WHERE artist_name = "New Order";
+----+
| id |
+----+
| 1 |
+----+
1 row inset sec)

Download 4.24 Mb.

Share with your friends: