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',2

drop 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的数据

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
*/