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

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.

1 comment:

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


How to interpret Disk Latency

I was analyzing IO stats in one of our SQL Servers and noticed that IO latency (Read / Write) are very high. As a rule of thumb, we know tha...