Aliasing tables in a SELECT statement example



By:

Overview
The purpose for aliasing a table is to have a short abbreviation for the table.  Typically a long table name can easily be aliased with one to three characters.  By doing so the code is typically much shorter and easier to read when troubleshooting.  Taking this small step will hopefully lead to a simpler next step with JOINs where table aliasing is key.  Let's take a look at an example. 

Explanation
Let's use the same query from an earlier tutorial with a few minor modifications for table aliasing.  In the example below, we are selecting the LoginID column aliased as 'Domain\LoginName' from the HumanResources.Employee table where the VacationHours column equals 8 and we are ordering the data by the HireDate in ascending order which is implied.  What is different is that we are aliasing the HumanResources.Employee table as 'E' to simplify the code in preparation for the JOIN examples.

USE AdventureWorks;
GO 
SELECT E.LoginID AS 'Domain\LoginName'
FROM HumanResources.Employee E
WHERE E.VacationHours = 8
ORDER BY E.HireDate;
GO

Below is the sample result set:


Last Update: 3/9/2009






Comments For This Article




Friday, July 29, 2016 - 9:36:09 AM - Greg Robidoux Back To Top (43005)

Hi D,

this example only has one table so the aliasing is not as helpful, but if you have many tables this helps instead of having to use the table name as the reference you can use the alias.

SELECT a.name, b.company, c.faxnumber, c.phonenumber
FROM person a
INNER JOIN company b ON a.companyid = b.companyid
INNER JOIN contactinfo c ON a.personid = c.personid


Friday, July 29, 2016 - 9:15:50 AM - D Back To Top (43004)

I think it would be very helpful in understanding the reasoning behind why you would use aliasing if you gave an example of what the query would look like if there was no aliasing for comparison.   Because, currently it looks confusingly as if the query would still work without the "E." in front of VacationHours = 8. 

This might also be helpful in the next page as well. 

 

Just a suggestion.

 



download


get free sql tips
agree to terms


Learn more about SQL Server tools