is it a bug in SQL Server 2008?
sql, sql-server-2008
Solution
No, it's not a bug.
You can see what's happening a bit clearer if you add table aliases to the fields used throughout the query:
select * from Mytable1 mt1
where mt1.Fname in (select mt1.Fname from Mytable2 mt2)
- ie. the subquery is referencing (and returning) values from the main query.
If you change the query to:
select * from Mytable1 mt1
where mt1.Fname in (select mt2.Fname from Mytable2 mt2)
- you get an error.
(SQLFiddle here)
Problem
``` create table Mytable1 (ID int, Fname varchar(50) ) create table Mytable2 (ID int, Lname varchar(50) ) insert into Mytable1 (ID,Fname) values (1,'you') insert into Mytable1 (ID,Fname) values (2,'Tou') insert into Mytable1 (ID,Fname) values (3,'Nou') insert into Mytable2 (ID,Lname) values (1,'you2') ``` The field `Fname` does not exist in table `Mytable2` But we have a result for the following query : ``` select * from Mytable1 where Fname in (select Fname from Mytable2) ``` Note : I use sql server 2008 the result is all rows of table Mytable1 is it a bug in SQL ?