MySQL column aliases

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

A result set takes its column headings from whatever you put in the select list. For a plain column that heading is the column name, which is usually fine. For a calculation it is the expression you typed, which is not. An alias replaces that heading with a name you choose. Open the client with mysql -u root -p sakila before you start.

How an alias works

SELECT COLUMN_OR_EXPRESSION AS ALIAS_NAME
FROM TABLE_NAME;

Replace COLUMN_OR_EXPRESSION with the column or calculation you want, ALIAS_NAME with the heading you want it to have, and TABLE_NAME with the table. The alias applies to the result only. Nothing about the table changes, and the next query knows nothing about it.

Examples

1) Rename a column

SELECT title AS film_title FROM film;

+-----------------------------+
| film_title                  |
+-----------------------------+
| ACADEMY DINOSAUR            |
| ACE GOLDFINGER              |
| ADAPTATION HOLES            |
| AFFAIR PREJUDICE            |
| AFRICAN EGG                 |
...
+-----------------------------+
1000 rows in set (0.00 sec)

The heading reads film_title. The values are untouched.

2) Name a calculation

This is where an alias earns its place. Without one the heading is the arithmetic.

SELECT title, rental_rate * 1.1 AS price_with_tax FROM film;

+-----------------------------+----------------+
| title                       | price_with_tax |
+-----------------------------+----------------+
| ACADEMY DINOSAUR            |          1.089 |
| ACE GOLDFINGER              |          5.489 |
| ADAPTATION HOLES            |          3.289 |
| AFFAIR PREJUDICE            |          3.289 |
| AFRICAN EGG                 |          3.289 |
...
+-----------------------------+----------------+
1000 rows in set (0.00 sec)

Application code reads results by column name, so an unnamed expression leaves that code depending on the exact text of your arithmetic.

3) An alias containing a space

An alias made of letters, digits and underscores needs no quoting. Anything else does, including a space or a word MySQL has reserved for its own use, such as order or select. Backticks are the quoting style to reach for.

SELECT title AS `Film Title` FROM film;

+-----------------------------+
| Film Title                  |
+-----------------------------+
| ACADEMY DINOSAUR            |
| ACE GOLDFINGER              |
| ADAPTATION HOLES            |
...
+-----------------------------+
1000 rows in set (0.00 sec)

Single quotes produce the same heading:

SELECT title AS 'Film Title' FROM film;

+-----------------------------+
| Film Title                  |
+-----------------------------+
| ACADEMY DINOSAUR            |
| ACE GOLDFINGER              |
| ADAPTATION HOLES            |
...
+-----------------------------+
1000 rows in set (0.00 sec)

They are not equivalent everywhere, though. Backticks mark a name, while single quotes mark a string, and a later clause that refers back to the alias can tell the difference. The next lesson shows that going wrong. Use backticks and the question never comes up. Leave the quotes off entirely and the server stops at the space:

SELECT title AS Film Title FROM film;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Title FROM film' at line 1

ERROR 1064 is the general syntax error. The quoted fragment at the end of the message is where the server gave up, which is normally a word or two after the real mistake.

AS is optional, and leaving it out hides a typo

A bare name after a column means the same as AS. That is also how a forgotten comma disguises itself.

SELECT title release_year FROM film;

+-----------------------------+
| release_year                |
+-----------------------------+
| ACADEMY DINOSAUR            |
| ACE GOLDFINGER              |
| ADAPTATION HOLES            |
...
+-----------------------------+
1000 rows in set (0.00 sec)

That was meant to be 2 columns. The missing comma turned release_year into an alias for title, so the result has 1 column, headed release_year, holding film titles. The server raised nothing, because the statement is valid. Writing AS on every alias does not make the server reject this, because neither column here has one. It does make the mistake visible when you read the query back: a bare name after a column, with no AS in front of it, is a missing comma.

Summary

Use AS to give a result column a name of your own.
Always alias a computed column, because its default heading is the expression.
Quote an alias with backticks when it contains a space or a reserved word.
Write AS on every alias, so a bare name reads as a missing comma.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

How an alias works

Examples

1) Rename a column

2) Name a calculation

3) An alias containing a space

AS is optional, and leaving it out hides a typo

Summary

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​How an alias works

​Examples

​1) Rename a column

​2) Name a calculation

​3) An alias containing a space

​AS is optional, and leaving it out hides a typo

​Summary

​See also

How an alias works

Examples

1) Rename a column

2) Name a calculation

3) An alias containing a space

AS is optional, and leaving it out hides a typo

Summary

See also