Use Aliases in SQL-Server 2022

Aliases in SQL Server 2022 can simplify queries, make them more readable, and reduce the amount of repetitive typing. They are essentially temporary names assigned to tables or columns within a query for ease of use. Here’s how you can use aliases effectively:

1. Column Aliases
   • A column alias gives a column a temporary name for the duration of the query.
   • Use the AS keyword to specify the alias (optional in many cases).
   • Example

     SELECT FirstName AS First, LastName AS Last
     FROM Employees;
     

     Output column names will be First and Last instead of FirstName and LastName.
   • You can also omit AS:

     SELECT FirstName First, LastName Last
     FROM Employees;
     

2. Table Aliases
   • A table alias provides a shorter or more convenient name for a table in a query.
   • Useful in queries involving joins or multiple references to the same table.
   • Example

     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 query would look like:

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

     Using aliases reduces verbosity.
3. Aliases in Calculations
   • Aliases make calculated fields clearer and easier to reference.
   • Example

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

     TotalCost is the alias for the calculated column Quantity * Price.
4. Aliases in Subqueries
   • Assign aliases to subqueries to reference their results.
   • Example

     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;
     

     sub is an alias for the subquery.

Tips for Using Aliases

• Use meaningful names: Choose aliases that describe their purpose (e.g., e for Employees).
• Avoid ambiguity: Ensure that aliases do not conflict with actual column or table names.
• Use square brackets for special characters: If your alias includes spaces or reserved keywords, enclose it in square brackets:

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

Aliases improve the maintainability and readability of your SQL queries, especially when working with complex joins, subqueries, or calculated fields.

An alias allows you to define an alternate name for the tables and columns in your queries. The two main advantages to using aliases are that they:

1. Force columns returned in a query to use an alternate name
2. Allow you to easily refer to a shorter table name within a query itself

To alias a column name, use the AS keyword in this syntax:
Alias:
An alternative name for a database object that makes referring to that object easier. For example, if you construct a query by specifying the fully-qualified name of the database (including server name, database name, owner, and table), it is easier to alias this long string of objects so that you do not have to specify it every time you access it's objects. Note, however, that an alias is useful only within a single Transact-SQL statement.

[Table.]column AS alias

For example, to return the Zip column in the Employees table as an alias named ZipCode, you could use this statement:

SELECT Zip AS ZipCode
FROM Employees

Issuing this Transact-SQL statement produces results similar to those shown in the illustration below:

Notice that the column name which is returned in the query is the alias name, ZipCode, not the actual name of the column, Zip.
Aliasing table names within your queries can save you a lot of typing. This is because you can alias the table with a shorter name, allowing you to reference the shorter name. Aliasing a table name is also done with the AS keyword, just as when you alias a column name. To illustrate this point, consider this join that uses aliases:

SELECT e.LastName, t.Hours
FROM Timesheets AS t join 
  Employees AS e ON e.EmployeeID = t.EmployeeID

Notice that after each table name, there is an AS keyword followed by a single letter. This single letter (or any other name that you choose) is the alias name. This alias name can then be used in place of the table name in any syntax that requires a table name. In the next lesson, you will learn how to join two select statements together with a union query.