The issue is referencing a column name that is not within the table variable within a stored procedure. I would assume this should have yielded an error, but it returns values instead as the column name is found in the main query.
Is this a known issue, fixed in SQL 2016 perhaps? Or is this behavior expected?
DECLARE @LookupTable TABLE(MyId Integer, PRIMARY KEY(MyId));
INSERT INTO @LookupTable (MyId) Values (1);
DECLARE @DataTable TABLE(My_Id Integer, MyOtherField Char(1), PRIMARY KEY(My_Id));
INSERT INTO @DataTable (My_Id,MyOtherField) Values (1,'A');
INSERT INTO @DataTable (My_Id,MyOtherField) Values (2,'B');
INSERT INTO @DataTable (My_Id,MyOtherField) Values (3,'C');
--SELECT MyId works as expected (one row)
SELECT MyOtherField FROM @DataTable
WHERE My_Id IN (SELECT MyId FROM @LookupTable)
--SELECT My_Id should be an error
--Returns three rows when referencing a column name not in @LookupTable
SELECT MyOtherField FROM @DataTable
WHERE My_Id IN (SELECT My_Id FROM @LookupTable)
--Returns expected error: invalid column name 'My_Id'
SELECT My_Id FROM @LookupTable
SQL Server 2014 (SP2).
M_Id column in sub-query is referred from outer table @DataTable. It is called correlated sub-query. The column from the outer table can be referred inside the sub-query.
Now it will make more sense.
SELECT MyOtherField FROM @DataTable T
WHERE T.My_Id IN (SELECT T.My_Id FROM @LookupTable)
Mostly the correlated sub-query are used when exists is used. You can see in Where clause the column from outer table will be referred