[not] in/exists 与 带TOP的子查询

当子查询中带TOP时，有连接条件和无连接条件结果是不一样的。

in和exists子句的结构也不一样。

看例子。

[@more@]

create table a(id int)

insert into a
select 1
union
select 2
union
select 3
union
select 4
union
select 5

create table b(id int)

insert into b
select 1
union
select 2
union
select 3

-- 1
select * from a
where id in(select top 2 id from b)
-- 2
select * from a
where id in(select top 2 id from b where b.id = a.id)
-- 3
select * from a
where exists(select top 2 id from b where b.id = a.id)
-- 4
select * from a
where exists(select 1 from (select top 2 id from b) c where c.id = a.id)

我们可以看出，1和4是正确的，3和4不是我盟想要的结果。
加上not也一样：

-- 1
select * from a
where id not in(select top 2 id from b)
-- 2
select * from a
where id not in(select top 2 id from b where b.id = a.id)
-- 3
select * from a
where not exists(select top 2 id from b where b.id = a.id)
-- 4
select * from a
where not exists(select 1 from (select top 2 id from b) c where c.id = a.id)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/66009/viewspace-1012948/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/66009/viewspace-1012948/