|
Posted by Roy Harvey (SQL Server MVP) on 10/24/07 18:04
On Wed, 24 Oct 2007 17:54:28 -0000, Anne <nienna.gaia@gmail.com>
wrote:
>Thanks!
>
>But why/how is colX "available" in both tables? Maybe there are some
>basics that I don't understand...
By available I meant that there is a column with that name in the
table.
Pretty much everywhere else in SQL if there are two tables with the
same column name, and you do not qualify a reference to the column
name, an error is returned that there is an ambiguous reference. The
one exception that I know about is with a subquery. INSIDE the
subquery the rules are a little different. First it tries to resolve
the un-qualified reference using the table(s) in the FROM clause of
the subquery. If there is only one table and it has that column, that
is the particular column of that name the subquery uses. If there are
multiple tables in the subquery FROM clause and only one of them has a
column of that name, that is the particular column of that name the
subquery uses. However, if NONE of the tables in the subquery's FROM
clause has that a column with that name, THEN it will look to resolve
the unqualified column name against the tables in the outer query's
FROM clause. If there is only one such reference it uses it, if there
is more than one it will error as an ambiguous reference.
I don't know if that is any clearer, but it is more complete.
Roy Harvey
Beacon Falls, CT
Navigation:
[Reply to this message]
|