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 Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 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 (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84543 Visits: 41064
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
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 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 Veteran
SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)

Group: General Forum Members
Points: 263 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
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 13
What irritates me about this is the use on lowercase for SQL keywords....... Looks amateurish
ALZDBA
ALZDBA
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11999 Visits: 8918
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
SSC Eights!
SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)

Group: General Forum Members
Points: 801 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 (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 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
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9501 Visits: 9740
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
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 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