3

I wrote a sql join query in oracle with two table. While writing query I did not use any table alias to refer column in select clause. Now this was possible because the selected columns were having different names in both the tables.

So the question follows; is it necessary (from performance point of view) to use a table alias to select column regardless there is any similar column name in both the tables?

Please note: Right now I am under impression that "In case of join query when we do not specify table alias to select column oracle will always lookup the table metadata to find out which table has this column." So is my assumption true?

Thanks, hanumant

hanumant
  • 1,091
  • 4
  • 15
  • 27

2 Answers2

4

No, your assumption is not true.

There are at least four reasons to use table aliases:

  • If the alias is shorter than the name it allows you to type less.
  • If you self-join a table you are required to give an alias to one or both of the tables to allow you to distinguish between them.
  • Derived tables must have an alias in some database systems (not Oracle though, I think).
  • The alias can make it clearer what the role of the table is in that particularly query.

There is no significant performance penalty from using them.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • 1
    so, how does oracle resolve the table from column name without alias, it must have to lookup its internal object table to find out the column-table relation right? – hanumant Oct 12 '11 at 06:03
  • Clearly it has to know which columns belong to which tables, but that's true whether or not you use table aliases. – Mark Byers Oct 12 '11 at 06:07
  • So if it has to look up something then does specifying table alias in select clause lowers oracles burden or still oracle look up its own table ? – hanumant Oct 12 '11 at 06:11
  • 1
    You need to understand that you're talking about something that if it did have an effect would be so trivially small that you could never make a valid test case to prove it exists. It already has to check if the column names are correct when parsing the SQL regardless of alias or not. – Seph Oct 12 '11 at 11:21
  • 1
    Just to add on Mark's answer, another reason to use aliases is if your SQL schema is going to change (especially in agile style development), someone could add a `status` column to the other table and then your query is broken, the cost of maintaining and checking many different queries in this situation is a heavy burden on the testers. – Seph Oct 12 '11 at 11:24
  • @Seph: That's a reason for qualifying the field names, not necessarily using aliases. It's entirely possible (if cumbersome) to qualify the field names using the full table names. – Allan Oct 12 '11 at 13:16
  • Thanks a lot for detailed explaination @Seph – hanumant Oct 15 '11 at 06:07
0

To make it simple...

This fails

select 
ID,
ID
from table1 
inner join table2  on table1.ID = table2.AnotherID

This doesn't fail

select 
a.ID,
b.ID
from table1 a
inner join table2 b on a.ID = b.AnotherID

The reason the first one fails is because the Optimization Engine does not know which table to pull the ID field from. With the alias - you give it a "hint" as to which table to use. The alias is just a substitute for the full table name.

You also have to be careful where you try to access the aliased tables. For more information on this check out the SQL order of operations.

tsells
  • 2,751
  • 1
  • 18
  • 20