none
关于NOT EXISTS和NOT IN RRS feed

  • 问题

  • 各位高手,现时我发现SQL里用 EXISTS是比用IN快,NOT IN比 IN慢N倍,NOT EXIST也很慢,有没有语法上或逻辑上可以替代的方法,网上找过相关的的说NOT EXIST比NOT IN快,但我觉得就算换成了NOT EXISTS也是慢,有没有更好的方法?谢谢~!
    2012年12月12日 3:06

答案

全部回复

  • 建议这几个都不要使用

    可以用left join替代。


    family as water

    • 已标记为答案 hzpemu 2012年12月12日 3:38
    2012年12月12日 3:32
  • 建议这几个都不要使用

    可以用left join替代。


    family as water

    确实代替NOT IN的效率高很多,只要where is null就可以了。
    2012年12月12日 3:38
  • 我不觉得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!


    2012年12月12日 5:50
  •  可以轻而易举的举出一个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!




    2012年12月12日 6:19
  • 先不讲效率, 除非是一对一,否则 LEFT JOIN 得到的结果和 NOT IN/EXISTS 有差异了
    2012年12月12日 6:20
  • 先不讲效率, 除非是一对一,否则 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!

    2012年12月12日 6:58
  • 其实我的例子大概是这样的。

    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

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

    2012年12月12日 8:08
  • 对于完全等价的查询(对于任何数据都得到相同结果的查询,才算是等价。),JOIN优于或等于EXISTS。

    不过EXISTS的可读性好得多。


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

    2012年12月12日 8:33
    版主
  • 对于完全等价的查询(对于任何数据都得到相同结果的查询,才算是等价。),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!


    2012年12月12日 10:32