INF: Subquery Column Can Be Resolved as Outer Query Column

• Microsoft SQL Server versions 6.5, 7.0

SUMMARY

When a column in a subquery does not exist in the table referenced in the subquery, the column can resolve to a table in the outerquery.

The following script demonstrates this behavior:

DROP TABLE test1
GO
DROP TABLE test2
GO
CREATE TABLE test1 (col1 INT, col2 INT)
GO
CREATE TABLE test2 (col3 INT, col4 INT)
GO
INSERT INTO test1 VALUES(1,1)
INSERT INTO test1 VALUES(2,2)
INSERT INTO test1 VALUES(3,3)
INSERT INTO test1 VALUES(4,4)
INSERT INTO test1 VALUES(5,5)
INSERT INTO test2 VALUES(1,1)
INSERT INTO test2 VALUES(2,2)
INSERT INTO test2 VALUES(3,3)
INSERT INTO test2 VALUES(4,4)
INSERT INTO test2 VALUES(5,5)
GO
SELECT * FROM test1
GO
DELETE test1
WHERE col1 IN (SELECT col1 FROM test2 WHERE col4=1)
GO
SELECT * FROM test1
GO 

MORE INFORMATION

Explicitly qualify the column names inside the subquery to override implicit assumptions about table names.

Additional query words:

Keywords          : kbSQLServ650 kbSQLServ700 
Version           : winnt:6.5,7.0
Platform          : winnt 
Issue type        : kbinfo 

Last Reviewed: June 10, 1999