Usages of SQL Server Table Aliases

by EvanJPalmer

group1

In my opinion, SQL Server Table Aliases are over used.

I often see SQL statements in which an alias is used to abbreviate the table name, then the alias is used to specify otherwise ambiguous columns.

For example:

SELECT o.Id
FROM Order o
JOIN Product p on o.ProductId = p.Id
where o.ProductId = 666

I think this abbreviation doesn’t lead to more readable code, and would argue the opposite is true.

Keeping in mind that code is written once, but read many times, I would write the above statement like this:

SELECT Order.Id
FROM Order
JOIN Product on Order.ProductId = Product.Id
where Order.ProductId = 666

Note that I did not abbreviate the table names. My reasoning for doing this is that there can be no confusion which table is being referenced – so there is one less mental step required, even when the reader is reading the code for the first time.

I have two arguments to back me up.

Firstly, to illustrate other patterns when we do not use these types of aliases, imagine this C# code:

var o = new Order();
o.Id = 1234;
var ps = _productRepository.GetByOrderId(o.Id);

foreach(var p in ps)
{
Console.Write(p.OrderId + " : " + p.Id )
}

I think most developers would agree that the variable names here are not useful in describing what we’re working with, and unnecessarily obfuscate the code.

Perhaps a better name would be something like:

var order = new Order();

or, even better,

var orderToFulfil = new Order();

With this example, we can already see some kind of intent being conveyed..

Secondly, to show when I feel an alias is useful, consider self joins. In the case of self joins an alias is required, because using the table name would not resolve ambiguity.

One option that appears to be common would be to use an abbreviation like this:

SELECT *
FROM Order o
JOIN Order o2 ON o.ParentId = o2.Id

I don’t think these alias’ help with the readability of the SQL or to show any kind of intent, so I advocate being more descriptive.

SELECT *
FROM Order originalOrder
JOIN Order reshippedOrder ON originalOrder.Id = reshippedOrder .ParentId

Again, hopefully here some kind of intent is beginning to become apparent.

Now, I’ve checked MSDN on their suggested usage and it seems they don’t agree with me. I think this is one of the  few cases in which I don’t agree wit the MSDN docs.

Advertisements