难道这是SQL Server的BUG ???
/*
写了个In条件的查询,手误写错了列名,发现竟然没报错! 百思不得其解,故来求救高手
具体请看代码
*/
--------------------
drop table #acreate table #a ( a1 varchar(90), a2 int)insert #a select 'aaa1',1union all select 'aaa2',2union all select 'aaa2',2drop table #bcreate table #b (b1 varchar(90), b2 int)insert #b select 'aaa2-1',1union all select 'aaa2-2',2union all select 'aaa2-2',3union all select 'aaa2-3',3SELECT * FROM #b---- select b1 from #a where a2=2 --直接执行这句会报错的,因为不存在b1列select * from #b where b1 in ( select b1 from #a where a2=2 ) ---表#a中不存在b1列, 没报错,出现了所有数据select * from #b where b1 in (select b1 from #a where b2<3 ) ---表#a中不存在b1列,也不存在b2列,没报错,查出了#b表中b2<3的数据select * from #b where b1 in (select b1 from #a where a2=2 and b2=1) --表#a中不存在b1和b2列,查出了#b表中b2=1的数据
declare @a table (aid int,acol varchar(1))insert into @aselect 1,'a' union allselect 2,'b' union allselect 3,'c' union allselect 4,'d' union allselect 5,'e'declare @b table (bid int,bcol varchar(1))insert into @bselect 1,'a' union allselect 2,'b' union allselect 3,'c' select * from @a where aid>1 and aid in (select aid from @b)/*aid acol----------- ----2 b3 c4 d5 e*/select * from @a where aid>1 and aid in (select bid from @b)/*aid acol----------- ----2 b3 c*/
