Aliases
Aliases are used to improve readability. Aliasing can be applied to tables or views in the FROM clause or to columns in the SELECT clause.
I. Aliasing Tables
  • By assigning an alias to a table, we can use the alias in the entire query instead of using the table name.
  • Using alias is required when self-join command is used.
  • An optional “AS” keyword can be used to create an alias.
SYNTAX I (Top number of rows):
SELECT T.Column1, T.Column2
FROM Table1 AS T
SYNTAX II (Without “AS” keyword):
SELECT T.Column1, T. Column2
FROM Table1 T
II. Aliasing Columns
  • Aliasing of columns are done only in the SELECT clause.
  • SQL SERVER does not allow referencing column by its alias in the WHERE, GROUP BY or HAVING clauses. However you can use refer to a column by its alias in the ORDER BY clause.
  • Just like aliasing table name, an optional “AS” keyword can be used to create an alias.
SYNTAX:
SELECT T.Column1 AS ‘Column 1’, T.Column1 + T.Column2 AS ‘Column 1 and 2’
FROM Table1 AS T
Table: Employee
EmployeeID FirstName LastName Title HireDate ManagerID
1 George Cruz CEO 12/8/2008 NULL
2 John Mathew Vice President 4/1/2009 1
3 Ethan William Director I 8/8/2009 2
4 Jacob Logan Director II 12/8/2011 3
5 Nathan Mateo Senior Manager 9/3/2009 4
6 Alice Charles Manager I 12/8/2012 5
7 John Robert Manager II 10/8/2011 6
8 Sophia James Manager III 5/8/2011 7
9 Kevin Chan Product Manager 7/8/2011 5
10 Susan Mortiz Solutions Arthitect 1/3/2012 6
11 Martha Miller Systems Arthitect 12/15/2012 10
Example 1 (Table alias)
Query :
SELECT E.EmployeeID, E.FirstName, E.LastName
FROM Employee AS E
WHERE E.EmployeeID < 6
Output :
EmployeeID FirstName LastName
1 George Cruz
2 John Mathew
3 Ethan William
4 Jacob Logan
5 Nathan Mateo
Description :
The above query uses an alias for the table Employee and selects all employees with EmployeeID less than 6.
Example 2 (Column alias)
Query :
SELECT EmployeeID AS ID, FirstName AS 'First Name', LastName AS [Last Name]
FROM Employee
WHERE EmployeeID < 6
Output :
ID First Name Last Name
1 George Cruz
2 John Mathew
3 Ethan William
4 Jacob Logan
5 Nathan Mateo
Description :
The above query uses column aliases to display more readable column names. Notice the different ways column aliases are defined. If the alias contains one or more spaces in between, it needs to be wrapped around in single quotes or in square brackets.
Example 3 (Column concatenations and alias)
Query :
SELECT EmployeeID AS ID, FirstName + ' ' + LastName AS [Full Name]
FROM Employee
WHERE EmployeeID < 6
Output :
ID Full Name
1 George Cruz
2 John Mathew
3 Ethan William
4 Jacob Logan
5 Nathan Mateo
Description :
The above query concatenates FirstName and LastName columns into a single column and uses column alias to display the result of concatenation as Full Name. Without using an alias, the concatenated column will not have any column name.
Example 4 (Column alias without AS keyword)
Query :
SELECT E.EmployeeID ID, E.FirstName 'First Name', E.LastName [Last Name]
FROM Employee E
WHERE E.EmployeeID < 6
Output :
ID First Name Last Name
1 George Cruz
2 John Mathew
3 Ethan William
4 Jacob Logan
5 Nathan Mateo
Description :
The above query uses both column and table aliases without using AS keyword.