Subquery with invalid column name runs

[Anne]

Hello!

Here is the statement in question:
--STATEMENT A
SELECT * FROM dbo.myTable WHERE colX in (SELECT colX FROM
dbo.sourceTable)

The problem with Statement A is that 'colX' does not exist in
'dbo.sourceTable'. It does, however, certainly exist in 'dbo.myTable'.
Breaking the statement down, we have:

--STATEMENT B
SELECT colX FROM dbo.sourceTable
...which returns the following error:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'colX'.

--STATEMENT C
SELECT colX FROM dbo.myTable
....which returns results,
If we modify Statement A to use a join:
--STATEMENT D
SELECT myTable.*
FROM dbo.myTable
JOIN dbo.sourceTable ON sourceTable.colX=myTable.colX
....we get the error:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'colX'.
Any idea what SQL Server is doing in Statement A?!?!

Thanks!
Anne

[Roy Harvey (SQL Server MVP)]

On Wed, 24 Oct 2007 14:42:23 -0000, Anne <ni*********@gmail.com>
wrote:

>Here is the statement in question:
--STATEMENT A
SELECT * FROM dbo.myTable WHERE colX in (SELECT colX FROM
dbo.sourceTable)

The problem with Statement A is that 'colX' does not exist in
'dbo.sourceTable'. It does, however, certainly exist in 'dbo.myTable'.
Breaking the statement down, we have:

>Any idea what SQL Server is doing in Statement A?!?!

In the subquery you can refer to any column in dbo.myTable or
dbo.sourceTable, they are both "available". Since the reference to
colx does not have a qualifier, and there is no colX in
dbo.sourceTable, the one from dbo.myTable is used. Had the column
existed in both tables, SQL Server would, by design, use the one from
table defined in the subquery for any un-qualified reference.

The best way to prevent this sort of thing is to always qualify the
column references in the subquery.

Roy Harvey
Beacon Falls, CT

[Anne]

Thanks!

But why/how is colX "available" in both tables? Maybe there are some
basics that I don't understand...

[Roy Harvey (SQL Server MVP)]

On Wed, 24 Oct 2007 17:54:28 -0000, Anne <ni*********@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

[Anne]

Yes! That is exactly what I was looking for. I needed to know what
steps SQL Server was taking to get to the point where the query ran in
the manner that it did. Thank you SO much!!!

[--CELKO--]

>But why/how is colX "available" in both tables? <<

colx is available to **the subquery** and not in both table at all.
Queries first try to reference the nearest column, and move outward in
the scope of query nesting.

>Maybe there are some basics that I don't understand... <<

Scoping rules in SQL are similar to the scoping rules in block
structure programming languages like Algol, C, PL/I, ADA, etc.

A: BEGIN
x INTEGER;
y INTEGER;
..
B: BEGIN
x INTEGER;
x := x + y;
..
END;
END;

When you get to block B, the local x is referenced in that assignment
statement, but the y in the containing block A is referenced.
But SQL is a bit more complicated. If two query expression are on the
same level, then you have to use a LATERAL operator in Standard SQL.
When a query expression is given a name and is it called a derived
table. Only that derived table is exposed to higher nesting level.

I have a chapter in THINKING IN SETS on this; the book is due out in
February 2008.