Let's consider two tables:
- MY_DB.dbo.MY_TABLE1 that does contain column MY_PROBLEMATIC_COLUMN
- MY_DB.dbo.MY_TABLE2 that does not contain column MY_PROBLEMATIC_COLUMN
As expected, query1:
SELECT MY_PROBLEMATIC_COLUMN FROM MY_DB.dbo.MY_TABLE2
Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'MY_PROBLEMATIC_COLUMN'.
However, this query2, which uses query1 as a subquery, works:
SELECT * FROM MY_DB.dbo.MY_TABLE1 t1 WHERE t1.MY_PROBLEMATIC_COLUMN IN ( SELECT MY_PROBLEMATIC_COLUMN FROM MY_DB.dbo.MY_TABLE2 )
And returns all rows from MY_DB.dbo.MY_TABLE.
How is it that erroneous is evaluated when used as subquery?
I don't believe it's crucial, but for a matter of fact, MY_PROBLEMATIC_COLUMN is of type varchar(50).
My first guess was that for some reason subquery is evaluated to NULL, yet then query2 would return 0 rows, as in NULL evaluates to false (I believe?)
DBMS: Sql Server 2000 (Sad, I know...)