Using The Dangerous In Clause In Sql

Why does SQL server behave this way. I am running this on SQL 2005. The IN clause does not validate the column names in the sub query but validates it against the table name in the

Solution 1:

Why? Because it's frequently useful to be able to reference columns from the outer query in subqueries. There's no setting you can use to turn off this behaviour, but if you get into the habit of using aliases, you should avoid most problems with it:

select * from#table1 t1where t1.col1 IN
(select t2.col1 from #table2 t2)

Will produce an error.

Solution 2:

It's not the IN clauses that's the problem.

This:

SELECT*FROM #table1
 WHERE col1 IN (SELECT col1 
                  FROM #table2)

...works, because the optimizer is assumes col1 to be from #table1. If you use table aliases so there's no ambiguity:

SELECT t1.*FROM #table1 t1
 WHERE t1.col1 IN (SELECT t2.col1 
                     FROM #table2 t2)

...you'll get the Msg 207: Invalid column error.

This is the same principle as when working with DELETE and UPDATE statements, because the typical syntax doesn't allow you to alias the table being deleted or updated.