数据库in/exists用法和效率大揭密

之前没注意到这两者的差别。

其实，这里还是有一定的陷阱的。

先看下代码：

[java] view plain copy

1. select count(*) from (  
2. ( select sc.xh from "JISUANJI"."STUDENTCHECK" sc )  
3.  union  
4.   ( select stu.XH as xh   
5.      from "JISUANJI"."CVARIABLE" cv , "JISUANJI"."SEMESTER" s , "JISUANJI"."STUBASICINFO" stu , "JISUANJI"."COURSE" c,"JISUANJI"."SCOURSE" sc  
6.     where  cv.SID = s.SID and cv.SCID = sc.SCID and sc.xkkh = c.coursecode and cv.XH = stu.XH and 1=1  
7.     and (stu.xh not exists (select sc.xh from "JISUANJI"."STUDENTCHECK" sc))  
8.     )  
9.   ) t1    

（运行错误，会报无效的关系运算符）

不细心地认为这是没有问题的，但是，它确实存在着差别。

[java] view plain copy

1. select count(*) from (  
2. ( select sc.xh from "JISUANJI"."STUDENTCHECK" sc )  
3.  union  
4.   ( select stu.XH as xh   
5.      from "JISUANJI"."CVARIABLE" cv , "JISUANJI"."SEMESTER" s , "JISUANJI"."STUBASICINFO" stu , "JISUANJI"."COURSE" c,"JISUANJI"."SCOURSE" sc  
6.     where  cv.SID = s.SID and cv.SCID = sc.SCID and sc.xkkh = c.coursecode and cv.XH = stu.XH and 1=1  
7.     and (stu.xh not in (select sc.xh from "JISUANJI"."STUDENTCHECK" sc))  
8.     )  
9.   ) t1    

运行正确

运行出错的代码应该改为：

[java] view plain copy

1. select count(*) from (  
2. ( select sc.xh from "JISUANJI"."STUDENTCHECK" sc )  
3.  union  
4.   ( select stu.XH as xh   
5.      from "JISUANJI"."CVARIABLE" cv , "JISUANJI"."SEMESTER" s , "JISUANJI"."STUBASICINFO" stu , "JISUANJI"."COURSE" c,"JISUANJI"."SCOURSE" sc  
6.     where  cv.SID = s.SID and cv.SCID = sc.SCID and sc.xkkh = c.coursecode and cv.XH = stu.XH and 1=1  
7.     and ( not exists (select sc.xh from "JISUANJI"."STUDENTCHECK" sc))  
8.     )  
9.   ) t1    

1.使用方式上的差别。

where stu.xh not in

where ont exits 

exists是不需要加上字段名字的。

有时候你会发现，运行好像没问题。

但是，它还是存在的问题的！数值也行有时候是正确的。这可能误导的！

[java] view plain copy

1. select count(*) from (  
2. ( select sc.xh from "JISUANJI"."STUDENTCHECK" sc )  
3.  union  
4.   ( select stu.XH as xh   
5.      from "JISUANJI"."CVARIABLE" cv , "JISUANJI"."SEMESTER" s , "JISUANJI"."STUBASICINFO" stu , "JISUANJI"."COURSE" c,"JISUANJI"."SCOURSE" sc  
6.     where  cv.SID = s.SID and cv.SCID = sc.SCID and sc.xkkh = c.coursecode and cv.XH = stu.XH and 1=1  
7.     and ( <span style="color:#ff0000;">not exists (select sc.xh from "JISUANJI"."STUDENTCHECK" sc where stu.xh= sc.xh</span>))  
8.     )  
9.   ) t1    

这才是最终的exists的使用方法。

exists关键字使用的时候，需要关联的！

为什么呢？

这个就是in/exists之间的原理的差别了

很多时候，有人跟你说，都使用exists，exists比in效率高！

但是，这个不是正确的了。可以说，exists使用比较多！

结论是：

<strong style="background-color:rgb(255,255,255)"><span style="color:#ff0000;">如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in</span></strong>

<span style="background-color:rgb(255,255,255)"><span style="color:#ff0000;">如果两个表都差不多一样大的话，效率是差不多的！</span></span>

这是因为，

in 是把外表和内表作hash join，而exists是对外表作loop，每次loop再对内表进行查询。每次循环查的时候，和in模式不一样的地方是：返回值是真或假，是真就输出。