Giving Names to Output Columns

3.4.1 Problem

You don't like the names of the columns in your query result.

3.4.2 Solution

Supply names of your own choosing using column aliases.

3.4.3 Discussion

Whenever you retrieve a result set, MySQL gives every output column a name. (That's how the mysql program gets the names that you see displayed as the initial row of column headers in result set output.) MySQL assigns default names to output columns, but if the defaults are not suitable, you can use column aliases to specify your own names.

This section explains aliases and shows how to use them to assign column names in queries. If you're writing a program that needs to retrieve information about column names, see Recipe 9.3.

If an output column in a result set comes directly from a table, MySQL uses the table column name for the result set column name. For example, the following statement selects three table columns, the names of which become the corresponding output column names:

mysql> SELECT t, srcuser, size FROM mail; +---------------------+---------+---------+ | t | srcuser | size | +---------------------+---------+---------+ | 2001-05-11 10:15:08 | barb | 58274 | | 2001-05-12 12:48:13 | tricia | 194925 | | 2001-05-12 15:02:49 | phil | 1048 | | 2001-05-13 13:59:18 | barb | 271 | ...

If you generate a column by evaluating an expression, the expression itself is the column name. This can produce rather long and unwieldy names in result sets, as illustrated by the following query that uses an expression to reformat the t column of the mail table:

mysql> SELECT -> CONCAT(MONTHNAME(t),' ',DAYOFMONTH(t),', ',YEAR(t)), -> srcuser, size FROM mail; +-----------------------------------------------------+---------+---------+ | CONCAT(MONTHNAME(t),' ',DAYOFMONTH(t),', ',YEAR(t)) | srcuser | size | +-----------------------------------------------------+---------+---------+ | May 11, 2001 | barb | 58274 | | May 12, 2001 | tricia | 194925 | | May 12, 2001 | phil | 1048 | | May 13, 2001 | barb | 271 | ...

The preceding example uses a query that is specifically contrived to illustrate how awful-looking column names can be. The reason it's contrived is that you probably wouldn't really write the query that waythe same result can be produced more easily using MySQL's DATE_FORMAT( ) function. But even with DATE_FORMAT( ), the column header is still ugly:

mysql> SELECT -> DATE_FORMAT(t,'%M %e, %Y'), -> srcuser, size FROM mail; +----------------------------+---------+---------+ | DATE_FORMAT(t,'%M %e, %Y') | srcuser | size | +----------------------------+---------+---------+ | May 11, 2001 | barb | 58274 | | May 12, 2001 | tricia | 194925 | | May 12, 2001 | phil | 1048 | | May 13, 2001 | barb | 271 | ...

To give a result set column a name of your own choosing, use AS name to specify a column alias. The following query retrieves the same result as the previous one, but renames the first column to date_sent:

mysql> SELECT -> DATE_FORMAT(t,'%M %e, %Y') AS date_sent, -> srcuser, size FROM mail; +--------------+---------+---------+ | date_sent | srcuser | size | +--------------+---------+---------+ | May 11, 2001 | barb | 58274 | | May 12, 2001 | tricia | 194925 | | May 12, 2001 | phil | 1048 | | May 13, 2001 | barb | 271 | ...

You can see that the alias makes the column name more concise, easier to read, and more meaningful. If you want to use a descriptive phrase, an alias can consist of several words. (Aliases can be fairly arbitrary, although they are subject to a few restrictions such as that they must be quoted if they are SQL keywords, contain spaces or other special characters, or are entirely numeric.) The following query retrieves the same data values as the preceding one but uses phrases to name the output columns:

mysql> SELECT -> DATE_FORMAT(t,'%M %e, %Y') AS 'Date of message', -> srcuser AS 'Message sender', size AS 'Number of bytes' FROM mail; +-----------------+----------------+-----------------+ | Date of message | Message sender | Number of bytes | +-----------------+----------------+-----------------+ | May 11, 2001 | barb | 58274 | | May 12, 2001 | tricia | 194925 | | May 12, 2001 | phil | 1048 | | May 13, 2001 | barb | 271 | ...

Aliases can be applied to any result set column, not just those that come from tables:

mysql> SELECT '1+1+1' AS 'The expression', 1+1+1 AS 'The result'; +----------------+------------+ | The expression | The result | +----------------+------------+ | 1+1+1 | 3 | +----------------+------------+

Here, the value of the first column is '1+1+1' (quoted so that it is treated as a string), and the value of the second column is 1+1+1 (without quotes so that MySQL treats it as an expression and evaluates it). The aliases are descriptive phrases that help to make clear the relationship between the two column values.

If you try using a single-word alias and MySQL complains about it, the alias probably is a reserved word. Quoting it should make it legal:

mysql> SELECT 1 AS INTEGER; You have an error in your SQL syntax near 'INTEGER' at line 1 mysql> SELECT 1 AS 'INTEGER'; +---------+ | INTEGER | +---------+ | 1 | +---------+