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