<

Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x

Why should I use aliases in my queries?

Published on
23,174 Points
2,574 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
Comment
  • 2
  • 2
4 Comments
 
LVL 49

Expert Comment

by:PortletPaul
Excellent overview, and good to refer "questioners" to. Thanks.
0
 
LVL 26

Expert Comment

by:Nick67
Yes, but...
When you actually have an understanding of the tables and fields involved, you have to go through the mental gymnastics of looking at data1 and referring back to the SQL to see that it is actually tbl_EE_1.data
When you start to work with REALLY complex SQL, this gymnastic process can be exhausting and error-prone.  It is very common to see SQL Server pros do this routinely, and MS Access pros do this almost never.  I think it drives from the culture and the environment, frankly.  MS Access has a very, very good GUI for building almost all the SQL strings you may ever use in that environment, and Access does a very good job of tracking what is involved.

You'd never write this SQL by hand in MS Access, and the GUI Join would never break with ambiguity, as the QBE looks after that.  In fact, most type 'Ambiguous column name 'pk'. ' come about BECAUSE you hand-edited the SQL adding aliases and now the editor can no longer determine your intentions.  Had the query been created solely by the editor, the error would have never manifested.
The query now returns 5 columns, of which 2 are named "pk", and 2 are named "data".  MS Access won't return columns with identical names and will in fact return [tablename].[fieldname] as a default when 2 identical fieldnames are set to be returned.

The GUI in SQL Server Management Studio is not as good, and you can build much more complex T-SQL that cannot be represented graphically.  In that environment, aliasing makes good sense, because the odds that you will typo complex table names are much greater, and it is an environment where the table and fieldnames tend to be created by forces OTHER than the dev and to be hopelessly complex, long, or not self-documenting.

In THAT case, aliasing is a survival skill, and most SQL Server pros are highly familiar with it and employ it extensively.  For an Access guy seeing this is confusing as hell:

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

and almost a certain way to flummox him/her.
The problem is the omission of AS which is used by Access in aliasing fields AND tables.
The following will make perfect sense to an MS Access guy

select t1.pk AS pk1, t1.data AS data1, t2.data AS data2 from tbl_EE_1 AS t1 join tbl_EE_2 AS t2 on t2.fk = t1.pk

It's all about where you are coming from :)
0
 
LVL 49

Expert Comment

by:PortletPaul
The problem is the omission of AS
Please just be aware that Oracle does NOT allow "as" for a table alias. Hence is you are writing often for dual environments, or aiming at transferable code, then using AS prior to a table alias can cause errors.
0
 
LVL 26

Expert Comment

by:Nick67
<grin>
It's all about where you are coming from
or figuring out where the Asker is trying to go.

I know when I first encountered SQL Server-style aliasing, my first thought is "how the hell did this get so badly obfuscated and how the hell is this meant to work?!?"
Nobody ever explained that the AS was implied.

Access, if you compose this
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
will turn it into

select t1.pk AS pk1, t1.data AS data1, t2.data AS data2 from tbl_EE_1 AS t1 join tbl_EE_2 AS t2 on t2.fk = t1.pk
so, yes, you have to know the audience you are aiming at.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month