[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)