When you join tables, it's often the case that the tables contain columns with the same names. If you refer to such a column in the query, it's ambiguous which table the column reference applies to. This ambiguity usually can be addressed by qualifying column names with table names. However, if you join a table to itself, even the table name is ambiguous and it's necessary to use aliases to disambiguate table references.
In SELECT query, output column names, by default, are the same as the column or expression selected. To rename a column, provide an alias following the column in the output list:
mysql> SELECT 1 AS One, 2+3 'Three Times Two';
+-----+-----------------+
| One | Three Times Two |
+-----+-----------------+
| 1 | 5 |
+-----+-----------------+
1 row in set (0.05 sec)
Here is another example:
mysql> SELECT Country.Name AS CountryName, City.Name AS CityName FROM Country, City
WHERE Country.Code = CountryCode;
Columns aliases are used as follows:
- The keyword AS is optional.
- An alias may be quoted. If it consists of multiple words, it must be quoted.
- You can refer to a column alias elsewhere in the query, in the GROUP BY, HAVING, or ORDER BY clause. However, you cannot refer to aliases in the WHERE clause.