/*
写了个In条件的查询,手误写错了列名,发现竟然没报错! 百思不得其解,故来求救高手
具体请看代码*/
--------------------------------------------------------drop table #a
create table #a ( a1 varchar(90), a2 int)
insert #a select 'aaa1',1
union all select 'aaa2',2
union all select 'aaa2',2drop table #b
create table #b (b1 varchar(90), b2 int)
insert #b select 'aaa2-1',1
union all select 'aaa2-2',2
union all select 'aaa2-2',3
union all select 'aaa2-3',3
SELECT * 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的数据
/*-----环境具体信息-----------------------------------------------------------------------------------------
环境:Win7旗舰版SP1
SQL Server 2008 R2Microsoft SQL Server Management Studio 10.50.1600.1
Microsoft Analysis Services 客户端工具 10.50.1600.1
Microsoft 数据访问组件 (MDAC) 6.1.7601.17514
Microsoft MSXML 3.0 4.0 6.0
Microsoft Internet Explorer 9.0.8112.16421
Microsoft .NET Framework 2.0.50727.5448
操作系统 6.1.7601
*/
写了个In条件的查询,手误写错了列名,发现竟然没报错! 百思不得其解,故来求救高手
具体请看代码*/
--------------------------------------------------------drop table #a
create table #a ( a1 varchar(90), a2 int)
insert #a select 'aaa1',1
union all select 'aaa2',2
union all select 'aaa2',2drop table #b
create table #b (b1 varchar(90), b2 int)
insert #b select 'aaa2-1',1
union all select 'aaa2-2',2
union all select 'aaa2-2',3
union all select 'aaa2-3',3
SELECT * 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的数据
/*-----环境具体信息-----------------------------------------------------------------------------------------
环境:Win7旗舰版SP1
SQL Server 2008 R2Microsoft SQL Server Management Studio 10.50.1600.1
Microsoft Analysis Services 客户端工具 10.50.1600.1
Microsoft 数据访问组件 (MDAC) 6.1.7601.17514
Microsoft MSXML 3.0 4.0 6.0
Microsoft Internet Explorer 9.0.8112.16421
Microsoft .NET Framework 2.0.50727.5448
操作系统 6.1.7601
*/
但是很容易理解的。
declare @a table (aid int,acol varchar(1))
insert into @a
select 1,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'d' union all
select 5,'e'declare @b table (bid int,bcol varchar(1))
insert into @b
select 1,'a' union all
select 2,'b' union all
select 3,'c'
select * from @a where aid>1 and aid in (select aid from @b)
/*
aid acol
----------- ----
2 b
3 c
4 d
5 e
*/
select * from @a where aid>1 and aid in (select bid from @b)
/*
aid acol
----------- ----
2 b
3 c
*/