Posted by Roy Harvey (SQL Server MVP) on 10/24/07 15:23

On Wed, 24 Oct 2007 14:42:23 -0000, Anne <nienna.gaia@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

[Back to original message]