sql server - Be careful when using T-Sql IN operator -

is there explain why kind of queries running id_t1=id_t1 because of in operator?

my colleague deleted whole table rows though wanted delete rows, when analyzed query realized there problem using "in" operator. query structure not proper ran. want show stuation how become:

create table #table1(id_t1 int) create table #table2(id_t2 int)  --insert data insert #table1(id_t1) values(1) insert #table1(id_t1) values(2) insert #table1(id_t1) values(3) insert #table1(id_t1) values(4)  insert #table2(id_t2) values(10) insert #table2(id_t2) values(20) insert #table2(id_t2) values(30) insert #table2(id_t2) values(40) 

as can see there no id_t1 in #table2, when run

select id_t1 #table2  id_t2 = 10  

query returns:

invalid column name 'id_t1'. 

but can using "in" operator

select * #table1 id_t1 in (select id_t1 #table2  id_t2 = 10) 

it returns whole #table1 rows

id_t1 --------- 1 2 3 4 

when use delete operation above

delete #table1 id_t1 in (select id_t1 #table2  id_t2 = 10) 

your #table1 have no more rows :)

select * #table1   id_t1 -----------  (0 row(s)   drop table #table1 drop table #table2 

---

whole of sample script copy/paste:

create table #table1(id_t1 int) create table #table2(id_t2 int)  --insert data insert #table1(id_t1) values(1) insert #table1(id_t1) values(2) insert #table1(id_t1) values(3) insert #table1(id_t1) values(4)  insert #table2(id_t2) values(10) insert #table2(id_t2) values(20) insert #table2(id_t2) values(30) insert #table2(id_t2) values(40)  select * #table1 id_t1 in (select id_t1 #table2  id_t2 = 10)   delete #table1 id_t1 in (select id_t1 #table2  id_t2 = 10)   select * #table1  drop table #table1 drop table #table2 

in select query

select * #table1 id_t1 in (select id_t1 #table2  id_t2 = 10) 

sql interprets as

select * #table1 #table1.id_t1 in (select #table1.id_t1 #table2  id_t2 = 10) 

so, each row in #table1, in clause value of #table1.id_t1, repeated each row of table 2. of course, every row qualifies.

this why should never run delete statement without checking select first...