Aaron has recently been doing an absolutely fantastic series of posts detailing various “bad habits” that many of us pick up somewhere along the way. These coding anti-patterns aren’t going to crash your server, but they will make your code more difficult to read and maintain. I’m enjoying Aaron’s series so much that I’ve decided to join in the fun with one of my own.
The “AS” keyword is optional both for table aliases and column name aliases. And although I’m pretty good about using it for columns, I’ve only recently started trying to break myself of the habit of not using it for tables. Let’s take a look at some code:
SELECT c.FirstName + ' ' + c.LastName FullName, c.Address, o.OrderDate, o.Subtotal + o.Shipping Total FROM Customer c INNER JOIN Order o ON o.CustomerId = c.CustomerId WHERE o.OrderId = 10
This code isn’t the most difficult thing in world to read, but I’ve tried to follow my ideal formatting practices in every way except for the AS keyword. This is a simple query, yet even here I find that my eye is scanning for something to differentiate the column names and the alias names. The AS keyword makes the eye snap to exactly the right spot when required:
SELECT c.FirstName + ' ' + c.LastName AS FullName, c.Address, o.OrderDate, o.Subtotal + o.Shipping AS Total FROM Customer AS c INNER JOIN Order AS o ON o.CustomerId = c.CustomerId WHERE o.OrderId = 10
For me this code speaks for itself. It’s just a tiny bit easier to read–enough that after a long day of coding I would probably make fewer mistakes maintaining this code than the previous version.
Another option is use of “=”, but I don’t like that for three reasons:
- It can’t be used everywhere. It only works in the column list, and not for table aliases. This means that I now have to use two different systems in my queries if I want to be complete.
- “=” is the assignment operator. It’s also the equality operator. Do we really need to give it yet another overload?
- It seems more logical to me to define what I’m naming, then name it. Using “=”, you’re doing the opposite. Here’s my name, and here’s what it is. Kind of like the difference between a LEFT OUTER JOIN and a RIGHT OUTER JOIN. And 9 out of 10 SQL developers seem to prefer the former, for good reason. That’s just the way our minds work.
Breaking yourself of this habit won’t take a lot of additional effort, but it just might pay off some late night when you’re forced to do a blurry-eyed emergency fix on some key piece of code and you don’t mess it up on the first shot. You can thank me the next morning.