Example
Column aliases are used mainly to shorten code and make column names more readable.
Code becomes shorter as long table names and unnecessary identification of columns (e.g., there may be 2 IDs in the table, but only one is used in the statement) can be avoided. Along with table aliases this allows you to use longer descriptive names in your database structure while keeping queries upon that structure concise.
Furthermore they are sometimes required, for instance in views, in order to name computed outputs.
All versions of SQL
Aliases can be created in all versions of SQL using double quotes (").
SELECT
FName AS "First Name",
MName AS "Middle Name",
LName AS "Last Name"
FROM Employees
Different Versions of SQL
You can use single quotes ('), double quotes (") and square brackets ([]) to create an alias in Microsoft SQL Server.
SELECT
FName AS "First Name",
MName AS 'Middle Name',
LName AS [Last Name]
FROM Employees
Both will result in:
| First Name | Middle Name | Last Name |
|---|---|---|
| James | John | Smith |
| John | James | Johnson |
| Michael | Marcus | Williams |
This statement will return FName and LName columns with a given name (an alias). This is achieved using the AS operator followed by the alias, or simply writing alias directly after the column name. This means that the following query has the same outcome as the above.
SELECT
FName "First Name",
MName "Middle Name",
LName "Last Name"
FROM Employees
| First Name | Middle Name | Last Name |
|---|---|---|
| James | John | Smith |
| John | James | Johnson |
| Michael | Marcus | Williams |
However, the explicit version (i.e., using the AS operator) is more readable.
If the alias has a single word that is not a reserved word, we can write it without single quotes, double quotes or brackets:
SELECT
FName AS FirstName,
LName AS LastName
FROM Employees
| FirstName | LastName |
|---|---|
| James | Smith |
| John | Johnson |
| Michael | Williams |
A further variation available in MS SQL Server amongst others is <alias> = <column-or-calculation>, for instance:
SELECT FullName = FirstName + ' ' + LastName,
Addr1 = FullStreetAddress,
Addr2 = TownName
FROM CustomerDetails
which is equivalent to:
SELECT FirstName + ' ' + LastName As FullName
FullStreetAddress As Addr1,
TownName As Addr2
FROM CustomerDetails
Both will result in:
| FullName | Addr1 | Addr2 |
|---|---|---|
| James Smith | 123 AnyStreet | TownVille |
| John Johnson | 668 MyRoad | Anytown |
| Michael Williams | 999 High End Dr | Williamsburgh |
Some find using = instead of As easier to read, though many recommend against this format, mainly because it is not standard so not widely supported by all databases. It may cause confusion with other uses of the = character.
All Versions of SQL
Also, if you need to use reserved words, you can use brackets or quotes to escape:
SELECT
FName as "SELECT",
MName as "FROM",
LName as "WHERE"
FROM Employees
Different Versions of SQL
Likewise, you can escape keywords in MSSQL with all different approaches:
SELECT
FName AS "SELECT",
MName AS 'FROM',
LName AS [WHERE]
FROM Employees
| SELECT | FROM | WHERE |
|---|---|---|
| James | John | Smith |
| John | James | Johnson |
| Michael | Marcus | Williams |
Also, a column alias may be used any of the final clauses of the same query, such as an ORDER BY:
SELECT
FName AS FirstName,
LName AS LastName
FROM
Employees
ORDER BY
LastName DESC
However, you may not use
SELECT
FName AS SELECT,
LName AS FROM
FROM
Employees
ORDER BY
LastName DESC
To create an alias from these reserved words (SELECT and FROM).
This will cause numerous errors on execution.
