SQL Queries - Use The Alias!!
Posted By : todd sharp Posted At : December 4, 2006 3:16 PM Posted In: SQL
9
Back to my SQL theme for today. I'm looking at a query right now that is driving me nuts. Just a tip - If you're going to write a query that joins multiple tables together, it's really a good idea to use the table alias within the query itself - otherwise other people who read that query will have a pretty darn hard time tracking down which columns came from which tables.
For example - the bad way:
select something,somethingElse, lotsOfOtherSomethingElses
from tblOne as a
inner join tblTwo as b
on a.fooId = b.fooId
lots of other joins...
from tblOne as a
inner join tblTwo as b
on a.fooId = b.fooId
lots of other joins...
The better way:
select a.something,b.somethingElse, c.lotsOfOtherSomethingElses
from tblOne as a
inner join tblTwo as b
on a.fooId = b.fooId
lots of other joins...
from tblOne as a
inner join tblTwo as b
on a.fooId = b.fooId
lots of other joins...
You can get away with not aliasing the columns when the column names are not shared between multiple tables, but it's just so damn hard to read when you don't.
--- Ben
I do prefer aliases believe it or not - isn't that strange? Just seems more readable to me.
But I do agree with the post's idea. Heck, I give anything with a join and alias for simplicity now, and later.
with that out of the way, i'm going on record as saying i actually prefer to not have the table names aliased (but they should be fully qualified...see above). yes, i know it cuts down on typing. And it arguably makes the code more readable...at first.
i find when i'm maintaining complex queries that have aliased table names, i spend more time poring thru the JOINs trying to find the appropriate table name that corresponds to 'c' (or whatever your favorite letter of the alphabet is).
i generally try to be pretty descriptive in my naming conventions. i find that helps me during the ongoing maintenance phase of a project. a table alias is really just a variable. if I have a variable that's supposed to hold a user name, i'm probably going to call it username. if i have a table that's already named 'username' (and let's just say for the sake of argument that for some reason, i do have a table with this name), why obscure it by aliasing it with a single letter?
i know i'm likely in the minority here and i'm not saying that not using aliases is "better". i'm saying it's more comfortable for me. i guess that just makes me kinda weird :)
seriously...is it just me? :)
Have you ever had a query that joins to the same table twice! That gets a bit strange and you have to come up with really descriptive aliasing so your head doesn't explode.
Bottom line, you have to maintain a delicate balance between readability and an extraneous effort.
SELECT Table1.Column1, Column2, Table1.Column3, Column4,
Table2.Column1, Column2
Table3. ETC ETC
I hate aliases. How am I supposed to know what "sc" or "s" or "r" are supposed to mean? I prefer to use the fully qualifed tablename.column (as Ben said )
For simple queries, with 2 or 3 joins, I prefer an alias.
On more complex queries with huge field lists, lots of tables, group, etc.. fqtn probably make more sense. On my dinky little sites, the query is generally no longer than a few lines and without even trying, i can usually see the aliases.
you've (we've) got to do whatever is best for the situation.