Databases Reference
In-Depth Information
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 in set (0.21 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 (42S22): 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 in set (0.05 sec)
