One of my colleagues asked me a question about a simple T-SQL query which uses table alias. See the below T-SQL code;
USE AdventureWorks2012
GO
SELECT E.loginID,HumanResources.Employee.JobTitle FROM HumanResources.Employee E
The above query used a table alias “E” and in SELECT list one column refers with table alias while the other column referring full table name. Seems like technically correct query. However the query returned the following error.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "HumanResources.Employee.JobTitle" could not be bound.
As per the error message, use of table name, “HumanResources.Employee” to refer JobTitle column is incorrect. When you remove HumanResources.Employee in the SELECT list then the query works fine.
The theory behind this is when you have a table alias, it logical rename the table to table alias. So if you want to refer the table in the query, it needs to use table alias instead of the actual table name.
Relational algebra explains this more clearly.
The relevant operator for table alias in relational algebra (RA) is RENAME. As SQL derived from RA it is always better to learn RA before learning SQL.
