Microsoft KB Archive/298674

= PRB: Subquery Resolves Names of Column to Outer Tables =

Article ID: 298674

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q298674



SYMPTOMS
A subquery that references a column which exists only in the main query returns results for the query.



CAUSE
In the case of an unqualified column name, the query optimizer assumes that the query is a correlated subquery. This behavior is correct and is documented in SQL Server Books Online. It is also in accordance with the current ANSI specification for the SQL language.



WORKAROUND
To avoid possible ambiguities in column references, use fully-qualified column names in the form . in complex queries. Incorrect column references are then identified by the SQL Server and the following error message is returned to the client:

Server: Msg 207, Level 16, State 3, Line 1

Invalid column name .



MORE INFORMATION
To illustrate the behavior, use the following two table structures and query: CREATE TABLE X1 (ColA INT, ColB INT) CREATE TABLE X2 (ColC INT, ColD INT) SELECT ColA FROM X1 WHERE ColA IN (Select ColB FROM X2) The query returns a result where the column ColB is considered from table X1.

By qualifying the column name, the error message occurs as illustrated by the following query: SELECT ColA FROM X1 WHERE ColA in (Select X2.ColB FROM X2)

Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'ColB'.

Keywords: kbprb kbpending KB298674

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.