Why No Error in T-SQL?

When the following SQL is run you don't get an error i开发者_JS百科n either SQL version 2005 or 2008 R2.

select 1 as MyVal, 'string' as MyText
into #table1

select 1 as thisColumnDoesntExistInTable1, 'string' as MyText
into #table2

select * from #table1
select * from #table2

-- WHY NO ERROR HERE ---    
select *
from #table2
where thisColumnDoesntExistInTable1 in
(
    select thisColumnDoesntExistInTable1 from #table1
)

drop table #table1
drop table #table2

But if you change the statement as follows by adding an alias to the inner select...

select *
from #table2
where thisColumnDoesntExistInTable1 in
(
    select a.thisColumnDoesntExistInTable1 from #table1 a
)

...you do get an error.

Effectively, you have this. So no error

select * from #table2 t2
where thisColumnDoesntExistInTable1 in
        (select t2.thisColumnDoesntExistInTable1 from #table1 )

When you qualify this to be explicit for table1, you get the error

The scope of the query is available in the subselect. You can see this more clearly if you change what's in #table2.

select 1 as MyVal, 'string' as MyText
into #table1

select 2 as thisColumnDoesntExistInTable1, 'string' as MyText
into #table2

select * from #table1
select * from #table2

select * from #table2 where thisColumnDoesntExistInTable1 in (select thisColumnDoesntExistInTable1 from #table1 )

drop table #table1
drop table #table2

So you can see, the result will show 2 instead of 1 because you're accessing the value of thisColumnDoesntExistInTable1 from #table2.

Column thisColumnDoesntExistInTable1 does not, as it says, exist in #table1. In the first query, when the compiler hits the subquery, since the column is not aliased it looks in all tables involved in the query, finds it in one, and uses it from there. In the second query, the column is aliased, so SQL only checks the referenced table for the column, doesn't find it, and throws the error.