The A, B, C’s of Table Aliases
Dear Developers, DBA’s and Power Users,
There is something that I’ve been seeing in your SQL that it annoys me so much that it’s forcing me to bring it to your attention. Developers, DBA’s and power users hear my plea. Table aliases are wonderful things. They make SQL easier to read and more compact. I’m so glad that you guys use them. That is until you do something like this:
SELECT a.title_id, a.title, c.au_id, c.au_fname + ' ' + c.au_lname name FROM titles a JOIN titleauthor b ON a.title_id = b.title_id JOIN authors c ON b.au_id = c.au_id
You’re really not making this easy for anyone to read are you? But do you stop there? Unfortunately not.
SELECT c.stor_id, c.stor_name, b.title_id, b.title, a.qty FROM sales a JOIN titles b ON a.title_id = b.title_id JOIN stores c ON a.stor_id = c.stor_id
So let me get this straight.,the Title table has an alias of “a” in one statement and then “c” in the next statement? How is that easy to read? How does this assist the next guy that has to maintain your code (i.e. me)? Please do me a favor. Use table aliases that make sense. Why not use “ti” for titles? You could even use ‘”t”. Here’s another tip: use it every time you the Title table use the same alias. There are tools that can do this for you. Redgate’s SQL Prompt is the first one that comes to mind. It will help you with that pesky a, b, c problem and it will help you write SQL faster. I’m going to stop there. I hope you get the point. Take a little more care with the code that you write you may the next person to look at it.
PS – I didn’t bring it up but your formatting is atrocious. You may want to work on that next. In order to help you with that I reformatted it for you. I hope you don’t mind.
SELECT st.stor_id , st.stor_name , ti.title_id , ti.title , sa.qty FROM Sales sa JOIN Titles ti ON sa.title_id = ti.title_id JOIN Stores st ON sa.stor_id = st.stor_id
Your friend,
Richie
Jun 29, 2012 @ 09:44:39
One of my big pet peeves. In all coding, SQL or not, I’m a firm believer that the code gets written once and read many, many times. And sometimes not in the best of circumstances.
I usually use very full names for SQL aliases. If I have a table called GRANTS.DBO.tblOrganizations the last thing in want to see it called in a complex query is A. Even O or TO is ok in a 5 line query, but in a larger query it’s just small enough to make me look back to verify it’s the table I think it is. I go all the way and alias it to Organizations. Now I can see fields like “Organization.ID” and “Organization.LegalName”. Yes a bit longer to write, but sooo much more readable when you’re trying to figure out why something’s wrong. And I don’t have to go back and make sure that in this case TO is an alias for tblOrganisations and not tblOffices.
Yes takes longer to write (not a huge problem since cut and paste came out decades ago). And takes more space, but not as much as one would think. But does make the query much more readable when you look back in three years.
Overlooking my fanaticism, I could certainly live with Org as a name (though may cringe a bit at Off or Offc rather than splurging the 2-3 characters for Office). But glad you brought up, because for sure A is a bad choice.
–Al-
Jun 29, 2012 @ 23:00:52
Thanks for the comment Al!
The problem that I have with full table names is that it makes the query much longer than it has to be. I hate to use the horizontal scroll but when you have a join with a fairly long column name you may have to use it. When you use consistent table aliases you get used to tbl_Organization being “o” or “org”. I’ve also seen tables that have very similar names look very similar leading to confusion.
That’s why I like SQL Prompt. It can automatically generate the alias for you. I also believe you can set an alias for a table so that it’s consistent.
That’s what I think the key is…consistency. Be consistent and at least I can follow along. But when you keep switching things around that’s when it gets rough.