Tuesday, June 25, 2013

“Table Alias”, How it behaves?

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.


RA


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.

1 comment:

  1. a good description of a commonly seen problem
    By mark kelly

    ReplyDelete

Transitioning of SQLOS to SQLPAL

​ The SQLOS​ Prior to SQL Server 2005, SQL Server runs as a user mode process like any other Windows application and it depends on...