SQL Server - Subquery - Be careful in passing column names in the subqueries

If a column is referenced in a subquery that does not exist in the table referenced by the subquery's FROM clause, but exists in a table referenced by the outer query's FROM clause, the query executes without error.
SQL Server implicitly qualifies the column in the subquery with the table name in the outer query.
Examples:


DECLARE @Tmp1 TABLE (I1 INT IDENTITY(1,1),Id1 INT,Name1 VARCHAR(10))
INSERT @Tmp1 SELECT 1,'Sathya'
INSERT @Tmp1 SELECT 2,'Deepak'
--SELECT * FROM @Tmp1

DECLARE @Tmp2 TABLE (I2 INT IDENTITY(1,1),Id2 INT,Name2 VARCHAR(10))
INSERT @Tmp2 SELECT 1,'Sathya'
INSERT @Tmp2 SELECT 3,'Sati'
--SELECT * FROM @Tmp2
 

--Column from table 1 is passed in both inner & outer query
SELECT * FROM @Tmp1 WHERE Id1 IN (SELECT Id1 FROM @Tmp2)
 
--Another Column of same data type from table 1 is passed in the inner query
SELECT * FROM @Tmp1 WHERE Id1 IN (SELECT I1 FROM @Tmp2)
 
--Be careful in passing column names in subqueries
DELETE FROM @Tmp1 WHERE Id1 IN (SELECT Id1 FROM @Tmp2)
SELECT * FROM @Tmp1




Reference:


 

See Also:

No comments: