in-clause scoping

Question

I am doing some work for a company that has SQL Server 2008. One of their stored procedures references (or appears to reference) a column that does not exist, and yet there is no error.

The query has this structure:

         select col1, col2 from FOO
         where col3 in
         (select id from BAZ where datecreated between @date1 and @date2)

** BAZ does not contain a column called [datecreated] but FOO does.** If I change the query to qualify the column name, as follows, there IS an error:

        select col1, col2 from FOO
         where b in
         (select id from BAZ where BAZ.datecreated between @date1 and @date2)

If this is the by-design behavior, could someone please point me to the relevant documentation? Thanks

score 3 · Accepted Answer · answered Jan 19 '12 at 14:26

3

It is by design.

It is perfectly valid to access a column from the outer query in a correlated sub query. This might be desired semantics in some cases.

answered Jan 19 '12 at 14:26

Martin Smith

438,706
87
741
845

+1 - It's normally considered good practice to qualify that column with the table name from the outer query for clarity, though. – JNK Jan 19 '12 at 14:29
@JNK - Yep, and failure to observe this [can lead to empty tables](http://stackoverflow.com/questions/4594733/sql-server-2008-management-studio-not-checking-the-syntax-of-my-query/4594765#4594765) if used in a `DELETE`! – Martin Smith Jan 19 '12 at 14:31

in-clause scoping

1 Answers1