Search code examples
sqlsql-server-2008scopingin-clause

in-clause scoping

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

Solution

  • 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.