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

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

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

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