Use Aliases to Make Queries Easier to Write

An alias is an alternative name for a database object, a column, a table, or even the result of a calculation, that makes referring to that object easier within a single query. Aliases make queries shorter, more readable, and less repetitive to type, which matters even more once a query involves joins, subqueries, or long fully-qualified object names.
An alias is useful only within the single Transact-SQL statement that defines it; it does not persist beyond that statement.

This matters most once a query starts specifying fully-qualified object names, server, database, owner, and table, or once the same table needs to appear in a query more than once. Typing that full chain out repeatedly, or writing Employees.EmployeeID on every line of a join, adds up fast; an alias lets you write it once and refer to the short version for the rest of the statement.

A column alias gives a column a temporary name for the duration of the query, using the AS keyword:

SELECT FirstName AS First, LastName AS Last
FROM Employees;

The output columns are named First and Last instead of FirstName and LastName. The AS keyword is optional in most cases; the same alias can be written without it:

SELECT FirstName First, LastName Last
FROM Employees;

The general syntax is:

[Table.]column AS alias

For example, to return the Zip column in the Employees table under the alias ZipCode:

SELECT Zip AS ZipCode
FROM Employees;

This produces results like the following:

ZipCode
-------
11111
12345
60609

(3 row(s) affected)

Notice that the column name returned in the result set is the alias, ZipCode, not the actual column name, Zip.

A table alias provides a shorter, more convenient name for a table within a query, which is especially useful in joins or whenever a query needs to reference the same table more than once:

SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID;

Here, e is an alias for Employees, and d is an alias for Departments. Without aliases, the same query would need to be written like this:

SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Table aliasing is also what makes joins involving the Employees and Timesheets tables from earlier in this module faster to write:

SELECT e.LastName, t.AnnualTime
FROM Timesheets AS t
JOIN Employees AS e ON e.EmployeeID = t.EmployeeID;

After each table name, the AS keyword is followed by a single letter, though any name you choose works just as well. That alias can then be used anywhere the table name itself would be required for the rest of the statement.

Aliases make a calculated field clearer and easier to reference:

SELECT OrderID, Quantity, Price, Quantity * Price AS TotalCost
FROM Orders;

TotalCost is the alias for the calculated column Quantity * Price, rather than a column that actually exists in the table.

A subquery can be given an alias too, so the rest of the statement can reference its results:

SELECT e.FirstName, e.LastName, sub.AverageSalary
FROM Employees e
JOIN (
    SELECT DepartmentID, AVG(Salary) AS AverageSalary
    FROM Employees
    GROUP BY DepartmentID
) sub ON e.DepartmentID = sub.DepartmentID;

Here, sub is the alias for the subquery itself, letting the outer query treat its result set like any other table.

• Use meaningful names. Choose aliases that describe their purpose, such as e for Employees, rather than arbitrary letters.
• Avoid ambiguity. Make sure an alias does not conflict with an actual column or table name elsewhere in the same statement.
• Use square brackets for special characters. If an alias includes spaces or a reserved keyword, enclose it in square brackets:

SELECT FirstName AS [First Name], LastName AS [Last Name]
FROM Employees;

Aliases improve the maintainability and readability of SQL queries, especially once joins, subqueries, or calculated fields are involved, which is most of the query writing you will do from here on.

A short recap before the next lesson:

• A column alias renames a column, or a calculated expression, for the duration of a single query, using AS (which is optional).
• A table alias gives a table a shorter name to reference within the same query, which is what keeps joins readable once more than one table is involved.
• A subquery can be aliased too, letting the outer query treat its result set like any other table.
• An alias only exists within the statement that defines it, so there is no risk of it leaking into, or conflicting with, a later query.

In the next lesson, you will learn how to join two select statements together with a union query.