<

Why should I use aliases in my queries?

Published on
23,549 Points
2,949 Views
6 Endorsements
Last Modified:
Approved
Why use Aliases ? In short: because we are all lazy, but still want to be efficient!

But there are also technical issues around this topic!

So let me start by this example:
Code is written for MS SQL, but the principle applies for all database types, normally
create table tbl_EE_1 ( pk int , data varchar(10) )
create table tbl_EE_2 ( pk int, fk int, data varchar(10) )
go
select * from tbl_EE_1 join tbl_EE_2 on fk = pk
go
drop table tbl_EE_1
drop table tbl_EE_2

Open in new window

The SELECT line will raise a error message:
SQL Server:Msg 209, Level 16, State 1, Line 3  -  Ambiguous column name 'pk'.
Oracle : ORA-00918: column ambiguously defined
For humans, it's obvious what the sql should do, but how should the sql engine know, at least without some artificial intelligence?

To solve the error, we need to specify for the "pk" column from which table it should be used for the join:
select * from tbl_EE_1 join tbl_EE_2 on fk = tbl_EE_1.pk

Open in new window


In general, you should prefix all your columns by the table name they come from, to make it clear from the first glance, where each column is coming from.
Even for a reader of the query, who doesn't know the table schema at all, it will become obvious... This applies for external consultants, posting code on forums, etc etc
select * from tbl_EE_1 join tbl_EE_2 on table_EE_2.fk = tbl_EE_1.pk

Open in new window

The big advantage of this is that queries written like that will not break with the error mentionned above, even if later on, a new column "fk" would be added to tbl_EE_1, which would make the "fk"  reference ambiguous.

The query now returns 5 columns, of which 2 are named "pk", and 2 are named "data". This gives us a couple of issues:
for normal application queries, SELECT * FROM should never be used, you should always only return the columns which are really needed.Your network team will be thankful for avoiding uneccessary data crossing the wires.
Some database libraries will fail on such queries, as some columns have the same name.
Some database libraries may result in the duplicate column names to be actually renamed
Some database librariers may result in the duplicate columns to "suddenly" take over the data from the first column that was named the same way
So, let's name and alias the columns accordingly, and only use what we need in the application, for example:
select tbl_EE_1.pk AS pk1, tbl_EE_1.data AS data1, tbl_EE_2.data AS data2 from tbl_EE_1 join tbl_EE_2 on table_EE_2.fk = tbl_EE_1.pk

Open in new window

This will be efficient and safe, but as you can see, long-winded for writing the sql, as you have to prefix for each column that full table name.
But you can use aliases for your tables also!
select t1.pk AS pk1, t1.data AS data1, t2.data AS data2 from tbl_EE_1 t1 join tbl_EE_2 t2 on t2.fk = t1.pk

Open in new window

As you can see, even if slightly longer than the original query code, it will be safe, very easy to read.
6
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free