PRB: Subquery Resolves Names of Column to Outer Tables (298674)

The information in this article applies to:

Microsoft SQL Server 2000 (all editions)
Microsoft SQL Server 7.0

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 <table>.<column> 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 <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'.

Modification Type:	Major	Last Reviewed:	10/16/2003
Keywords:	kbpending kbprb KB298674