SQL SELECT Using Column Aliases


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 NameMiddle NameLast Name
JamesJohnSmith
JohnJamesJohnson
MichaelMarcusWilliams

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 NameMiddle NameLast Name
JamesJohnSmith
JohnJamesJohnson
MichaelMarcusWilliams

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  
FirstNameLastName
JamesSmith
JohnJohnson
MichaelWilliams

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:

FullNameAddr1Addr2
James Smith123 AnyStreetTownVille
John Johnson668 MyRoadAnytown
Michael Williams999 High End DrWilliamsburgh

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  
SELECTFROMWHERE
JamesJohnSmith
JohnJamesJohnson
MichaelMarcusWilliams

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.