|
|
Posted by Mike S on 06/21/06 16:25
Hi all,
A (possibly dumb) question, but I've had no luck finding a definitive
answer to it. Suppose I have two tables, Employees and Employers, which
both have a column named "Id":
Employees
-Id
-FirstName
-LastName
-SSN
etc.
Employers
-Id
-Name
-Address
etc.
and now I perform the following join:
SELECT Employees.*, Employers.*
FROM Employees LEFT JOIN Employers ON (Employees.Id=Employers.Id)
The result-set will contain two "Id" columns, so SQL Server will
disambiguate them; one column will still be called "Id", while the
other will be called "Id1." My question is, how are you supposed to
know which "Id" column belongs to which table? My intuition tells me,
and limited testing seems to indicate, that it depends on the order in
which the table names show up in the query, so that in the above
example, "Id" would refer to Employees.Id, while "Id1" would refer to
Employers.Id. Is this order guaranteed?
Also, why does SQL Server use such a IMO brain-damaged technique to
handle column name conflicts? In MS Access, it's much more
straightforward; after executing the above query, you can use
"Employees.Id" and "Employers.Id" (and more generally,
"TableNameOrTableAlias.ColumnName") to refer to the specific "Id"
column you want, instead of "Id" and "Id1" -- the
"just-tack-on-a-number" strategy is slightly annoying when dealing with
complex queries.
--
Mike S
Navigation:
[Reply to this message]
|