关于NOT EXISTS和NOT IN

Question

各位高手，现时我发现SQL里用 EXISTS是比用IN快，NOT IN比 IN慢N倍，NOT EXIST也很慢，有没有语法上或逻辑上可以替代的方法，网上找过相关的的说NOT EXIST比NOT IN快，但我觉得就算换成了NOT EXISTS也是慢，有没有更好的方法？谢谢~！

Answer 1

建议这几个都不要使用

可以用left join替代。

---

family as water

Answer 2

建议这几个都不要使用

可以用left join替代。

---

family as water

确实代替NOT IN的效率高很多，只要where is null就可以了。

Answer 3

我不觉得left join的效率比NOT EXIST、NOT IN高，相反NOT EXIST、NOT IN至少不会差于left join。而NOT EXIST、NOT IN他们的本身性能差不多，但语意不同。

效率高低看计划，计划的本质看算法，

无论从NOT EXIST、NOT IN的Left Anti Semi Join算法来讲，都不会比left join的Left Outer Join算法效率差，除非你没有合适的INDEX。

---

Please click the Mark as Answer button if a post solves your problem!

Answer 4

 可以轻而易举的举出一个NOT EXIST 比left join效率高或者不会差的例子 ，我不知道有没有反过来的例子？

下面的例子A.ID 分别用 9999，10000，10001 来测试，LEFT JOIN 的COST分别占总COST的50%，50%，92%

 CREATE TABLE [dbo].[Table_5](
 [ID] [int] NULL,
 [C2] [nchar](100) NULL
) ON [PRIMARY]
go

insert into  [dbo].[Table_5] values(9999,'')
insert into  [dbo].[Table_5] values(10000,'')
insert into  [dbo].[Table_5] values(10001,'')
go
 CREATE TABLE [dbo].[Table_4](
 [ID] [int] NOT NULL,
 [C2] [nchar](100) NULL
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [NonClusteredIndex-20121212-133949] ON [dbo].[Table_4]
(
 [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

declare @i int
set @i=1;

while (@i<10000)
begin
INSERT INTO [dbo].[Table_4] VALUES(@i,'')
 set @i=@i+1 ;
end

 GO

INSERT INTO [dbo].[Table_4] VALUES(10001,'')
 GO 10000

SET  statistics profile on
go
SELECT A.* FROM [dbo].[Table_5] A
LEFT JOIN [dbo].[Table_4] B
ON A.ID=B.ID
WHERE  A.ID =10001 AND   B.ID IS NULL

SELECT * FROM [dbo].[Table_5] A
WHERE  A.ID =10001 AND  NOT  exists  ( SELECT * FROM
  [dbo].[Table_4]
  WHERE A.ID= ID)

SET  statistics profile off
go

---

Please click the Mark as Answer button if a post solves your problem!

Answer 5

先不讲效率， 除非是一对一，否则 LEFT JOIN 得到的结果和 NOT IN/EXISTS 有差异了

Answer 6

先不讲效率， 除非是一对一，否则 LEFT JOIN 得到的结果和 NOT IN/EXISTS 有差异了

就是说从关系理论的角度来说，它们也是不能代替的，更不用说效率了，是吧。

如果是LEFT JOIN 跟EXISTS 确实是这个样子的。

不过LEFT JOIN& IS NULL 跟 NOT IN/EXISTS ，我似乎没有找到这样的例子，有例子吗？

---

Please click the Mark as Answer button if a post solves your problem!

Answer 7

其实我的例子大概是这样的。

A表                                           B表

ID      COUNT                       KEYID    ID     MEMO   

A       2                                1             A     DFASDF

B       3                                2             A      WEPIWUER

C       4                                3            B        EFWEF

用NOT IN写是这样的

select * from a where a.id not in

(

select DISTINCT b.id from b)

用left join 写是这样的

select * from

(

select a.id,a.count,b.id as bid from a left join b on a.id=b.id ) as c where c.bid is null

各位高手，你们觉得这样会不会有不一样的结果？这种情况能代替吗？

Answer 8

对于完全等价的查询（对于任何数据都得到相同结果的查询，才算是等价。），JOIN优于或等于EXISTS。

不过EXISTS的可读性好得多。

---

想不想时已是想，不如不想都不想。

Answer 9

对于完全等价的查询（对于任何数据都得到相同结果的查询，才算是等价。），JOIN优于或等于EXISTS。

不过EXISTS的可读性好得多。

---

想不想时已是想，不如不想都不想。

1,你说的JOIN，EXISTS包含NOT EXIST，left join 不？如果包含， 我上面的例子算完全等价的查询不？ 如果算，很明显A.ID =10001的例子，NOT EXIST的性能明显优于left join，NOT EXIST只占了总体COST的8%，而left join占了92% 。（并且可以从COST的算法上很容易的证明这一点）

2，“对于完全等价的查询（对于任何数据都得到相同结果的查询，才算是等价。），JOIN优于或等于EXISTS。”

能否举个JOIN优于EXISTS的例子？

从理论上说 JOIN用的是 Left Outer Join/Inner Join 算法，EXISTS是用的是 Left Semi Join，从时间复杂度来计算，他们不会有本质的差别。

而 NOT EXIST、NOT IN用的是Left Anti Semi Join 算法，某些情况下，从时间复杂度来计算，明显优于非 Anti  的Join 算法，所以就有上面例子的结果。

---

Please click the Mark as Answer button if a post solves your problem!