SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Enhancing the readability of your code: Table aliasing in sql


Enhancing the readability of your code: Table aliasing in sql

Author
Message
Sylvia Moestl Vasilik
Sylvia Moestl Vasilik
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 393
Comments posted to this topic are about the item Enhancing the readability of your code: Table aliasing in sql
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)

Group: General Forum Members
Points: 339691 Visits: 42624
But in this particular SQL statement, there's no reason to alias your tables names - it would just save a few keystrokes, at the cost of making it much less readable. Here's what I would do.


I apologize but I must strongly disagree with that statement. While it may appear to make it more readable for you at the time you're writing it, the next poor slob that has to troubleshoot the code will likely not be as intimately familiar with the tables as you are. (S)he will end up having to lookup which tables contain which columns and that takes a comparably long time even if you only have two tables joined. To wit, the practice of not using table aliases on joined queries is probably worse than the practices of either selecting nonrelated aliases or using full table names on every column reference.

And, heh... don't get me started on the practice of using leading commas in code.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Leendert van Staalduinen
Leendert van Staalduinen
SSC Veteran
SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)

Group: General Forum Members
Points: 281 Visits: 397
From my own practice: I always use table aliases when two or more tables will be JOINed (and never use an alias if no JOIN is involved). Advantages:
- I always understood that the SQL engine will be faster, if field names will be fully qualified (when working within a single database: having schema plus tablename prefices): the engine does not have to find out which schema/table contains the specified field.
- Readability: I certainly prefer to read tens of "TT."-s above tens of "dbo.tblTrabeculectomyTechniques."-s. When I see my SQL code after a few years, I feel comfortable (and I assume my collegues will feel comfortable as well).

Another suggestion: I always use a syntax like: "dbo.tblTrabeculectomyTechniques As TT", instead of "dbo.tblTrabeculectomyTechniques TT". I find this much easier to read.

Leendert.
Mike DiRenzo
Mike DiRenzo
SSC Eights!
SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)

Group: General Forum Members
Points: 847 Visits: 210
Sorry, but I agree with Jeff Moden's reply. The leading comma thing is a bit irritating but to each his/her own. With one or more tables it is not a bad idea to alias but Jeff's point is spot on with regard to the "next poor slob". I can't agree with you more Jeff.

-Mike
Gene Marini
Gene Marini
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 13
What irritates me about this is the use on lowercase for SQL keywords....... Looks amateurish
ALZDBA
ALZDBA
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47811 Visits: 9070
I advise to use aliasses in all queries that involve more than one table, view, ...

This is really not a comparable issue for using leading commas, upper / lower case on key words,... just because those will not generate "ambigous column names" in a case INsensitive db.

Using aliasses will help anyone analysing the query and figuring out from which objects the intended columns come from.

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Samuel Vella
Samuel Vella
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3119 Visits: 2144
Gene Marini (5/7/2009)
What irritates me about this is the use on lowercase for SQL keywords....... Looks amateurish


Each to their own again... I prefer proper case as personally I find it more readable. Keywords are highlighted differently anyway (working on the assumption that their aren't many cases where a developer would be working without syntax highlighting).
Interestingly UK (and many other countries) road signs are always in proper case e.g. London instead of LONDON as proper case is quicker to read

Code readability, much like object naming conventions usually comes down to systems. As long as there is a system in place, e.g. always trailing commas, always upper case for keywords and always using the same aliases for the same main group of tables (every system has a core of at most 10 tables which are used in 90% of the queries) then it doesn't matter what the system is as long as it makes sense and is stuck to within a team
David Hutcheson
David Hutcheson
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 100
What Jeff Moden said.

I totally agree with his point about leading commas. It might save you an error or two when you forget to delete it from the end of the previous row, but you have completely destroyed the readability of it.
David Burrows
David Burrows
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23328 Visits: 10364
And, heh... don't get me started on the practice of using leading commas in code.

Oh! Come on Jeff don't be coy now w00t

For the record, when I code SQL I,

Use trailing commas
Aliases when more than one table/subqeury
Aliases always in subqueries
Try to make alias meaningful (ie o for Order, ol or OrderLine etc)
Uppercase SQL keywords
Indent JOIN beneath FROM
Indent ON/AND beneath JOIN (each comparison on separate line)
Indent subqueries and subquery SQL


Far away is close at hand in the images of elsewhere.

Anon.


lfallis
lfallis
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 178
Each to their own again... I prefer proper case as personally I find it more readable. Keywords are highlighted differently anyway (working on the assumption that their aren't many cases where a developer would be working without syntax highlighting).
Interestingly UK (and many other countries) road signs are always in proper case e.g. London instead of LONDON as proper case is quicker to read

Code readability, much like object naming conventions usually comes down to systems. As long as there is a system in place, e.g. always trailing commas, always upper case for keywords and always using the same aliases for the same main group of tables (every system has a core of at most 10 tables which are used in 90% of the queries) then it doesn't matter what the system is as long as it makes sense and is stuck to within a team


I agree also (although being from England that may be why).

For anyone who has read a book on GUI's, most would advise to stay away from CAPITALS as they block out the screen more and make it more difficult to read. I always prefer lowercase, using capitals where words are joined and aliases even where there are no joins i.e. from myTable mt join anotherTable at on...

You never know when you are going to have to add a join in, and then you will need to go through and add them for the possible duplicate columns.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search