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
The SELECT line will raise a error message:
select * from tbl_EE_1 join tbl_EE_2 on fk = tbl_EE_1.pk
select * from tbl_EE_1 join tbl_EE_2 on table_EE_2.fk = tbl_EE_1.pk
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.
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
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.
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
As you can see, even if slightly longer than the original query code, it will be safe, very easy to read.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (4)
Commented:
Commented:
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 :)
Commented:
Commented:
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.