A common gripe I have when reviewing and maintaining other people’s SQL, aside from the obvious readability due to poor formatting, is their lack of aliases. In this post, I’d like to talk about why you should always reference aliases. The one major reason why, and the one minor reason when not to.
The one reason to?
Maintainability: When adding a join which shares column names.
The Error: Ambiguous Column Name
Often times the simplest of query blocks will contain as little as one join, maybe two. These simple queries are seemingly harmless and can be written without much care to alias detail. But, the moment the query needs to be modified/maintained where a column is added to the select which is in multiple tables of the query, now you’ve got to add an alias and spend time doing what should have been in the first place. Always reference aliases!
When not to?
The only time I can think of to not reference an alias throughout your query is if the query is, in fact, a single table select. There are cases when you simply cannot, like in the target of an update statement, or when writing DDL statements. But, still, I would advise against not aliasing whenever you can.
Rule of thumb?
I think a simple way to look at this is, if you’re referencing one or many tables and the query isn’t a SELECT *, then always reference aliases. It’s that simple!