November 2, 2009 at 10:57 am
I've seen several debates about whether a query will or won't run faster if you use table aliases, but I have a question that goes just a tad deeper.
If you join 2 tables in a query, and each table has an alias, but you don't use the alias to select a column from one of the tables (assuming the column name only exists in one of the tables), will the SQL compiler have to go to the table definition to determine which table to pull data from? I'm wondering if a non-aliased column in a joined query will run slower or less efficient (maybe more compiles) than an aliased column.
Thanks.
November 2, 2009 at 8:42 pm
My take on table aliases is simple, use them even in single table queries. Two reasons for this, first, you never know when an additional table may be added to the query. Second, you also never know when a change to a table may result in a column with the same name being added to a table causing an ambiguous column name error occurring.
November 2, 2009 at 11:40 pm
I Think it's better to use an alias when you have to create multiple instance of same table otherwise you can continue with table name itself. Why to give extra work of creating alias if it could be avoid.:-)
November 3, 2009 at 12:02 am
There are many reasons why to use the table aliases. some of them are :
1. These are helpful when you are joining a table to itself.
2. Joining a same table twice in same query.
3. You can call it as a basic abstractiion.
4. using aliases in a dynamic query where the table name is coming dynamically, can prevent that query [if it is a stored procedure] to execute a sql injection as it will throw an error.
5. Improves readability in complex queries.
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 3, 2009 at 10:16 pm
Hi
Theoretically speaking, the engine has to resolve the table in which the column exists in any statement.if there is a column with the same name in 2 tables and if those tables are participating in a join then adding aliases to the tables and subsequently to the columns would help the engine resolve the columns faster.if this difference is big then You might see a performance gain.if the difference in negligible then the human eye might not see it at all.
But more than the performance aspect of it, its always safe to use aliases even when there are are no columns with common names.because in future if someone adds a column with coulmn name which is already there in the other table, the query would still continue to run if aliases have been used.
November 4, 2009 at 12:32 am
aravind.s (11/3/2009)
if there is a column with the same name in 2 tables and if those tables are participating in a join then adding aliases to the tables and subsequently to the columns would help the engine resolve the columns faster.
I dont think you have any option here but to use aliases as otherwise it would cry sayin ambigous column!
---------------------------------------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply