Geek Answers Handbook

Is this a bug in SQL Server 2008?

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 Fname field Fname not exist in the table Mytable2 But we have the result for the following query:

 select * from Mytable1 where Fname in (select Fname from Mytable2)

Note: I am using SQL Server 2008, the result is all the rows of the table Mytable1

Is this a bug in SQL?

+4
source share
2 answers

No, this is not a mistake.

You can see what happens a bit clearer if you add table aliases to the fields used in the query:

 select * from Mytable1 mt1 where mt1.Fname in (select mt1.Fname from Mytable2 mt2)

- i.e. a subquery refers to (and returns) the values ​​from the main query.

If you change the request to:

 select * from Mytable1 mt1 where mt1.Fname in (select mt2.Fname from Mytable2 mt2)

- you will get an error message.

(SQLFiddle here )

+6
source

No, this is not an error: http://bugs.mysql.com/bug.php?id=26801

Apparently this means Fname from Mytable1 :

 mysql> select *, (select Lname from Mytable1 limit 1) from Mytable2 where Lname in (select Lname from Mytable1 ); +------+-------+--------------------------------------+ | ID | Lname | (select Lname from Mytable1 limit 1) | +------+-------+--------------------------------------+ | 1 | you2 | you2 | +------+-------+--------------------------------------+ 1 row in set (0.01 sec)
0
source

More articles:


All Articles