Question

SQL语句中“in”和“exist”有什么区别？

Answer 1

本文主要分析了in和exists的区别与执行效率的问题：\x0d\x0ain可以分为三类：\x0d\x0a\x0d\x0a1、形如select*fromt1wheref1in('a','b')，应该和以下两种比较效率。\x0d\x0a\x0d\x0aselect*fromt1wheref1='a'orf1='b'\x0d\x0a\x0d\x0a或者\x0d\x0a\x0d\x0aselect*fromt1wheref1='a'unionallselect*fromt1f1='b'\x0d\x0a你可能指的不是这一类，这里不做讨论。\x0d\x0a\x0d\x0a2、形如\x0d\x0a\x0d\x0aselect*fromt1wheref1in(selectf1fromt2wheret2.fx='x')，\x0d\x0a\x0d\x0a其中子查询的where里的条件不受外层查询的影响，这类查询一般情况下，自动优化会转成exist语句，也就是效率和exist一样。\x0d\x0a\x0d\x0a3、形如\x0d\x0a\x0d\x0aselect*fromt1wheref1in(selectf1fromt2wheret2.fx=t1.fx)，\x0d\x0a其中子查询的where里的条件受外层查询的影响，这类查询的效率要看相关条件涉及的字段的索引情况和数据量多少，一般认为效率不如exists。\x0d\x0a\x0d\x0a除了第一类in语句都是可以转化成exists语句的，一般编程习惯应该是用exists而不用in.\x0d\x0a\x0d\x0aA，B两个表，\x0d\x0a\x0d\x0a(1)当只显示一个表的数据如A，关系条件只一个如ID时，使用IN更快：\x0d\x0a\x0d\x0aselect*fromAwhereidin(selectidfromB)\x0d\x0a\x0d\x0a(2)当只显示一个表的数据如A，关系条件不只一个如ID，col1时，使用IN就不方便了，可以使用EXISTS：\x0d\x0a\x0d\x0aselect*fromAwhereexists(select1fromBwhereid=A.idandcol1=A.col1)\x0d\x0a\x0d\x0a(3)当只显示两个表的数据时，使用IN，EXISTS都不合适，要使用连接：\x0d\x0a\x0d\x0aselect*fromAleftjoinBonid=A.id\x0d\x0a\x0d\x0a所以使用何种方式，要根据要求来定。\x0d\x0a\x0d\x0a这是一般情况下做的测试：\x0d\x0a\x0d\x0a测试结果:\x0d\x0a\x0d\x0asetstatisticsioonselect*fromsysobjectswhereexists(select1fromsyscolumnswhereid=syscolumns.id)select*fromsysobjectswhereidin(selectidfromsyscolumns)setstatisticsiooff(47行受影响)\x0d\x0a\x0d\x0a表'syscolpars'。扫描计数1，逻辑读取3次，物理读取0次，预读2次，lob逻辑读取0次，lob物理读取0次，lob预读0次。\x0d\x0a\x0d\x0a表'sysschobjs'。扫描计数1，逻辑读取3次，物理读取0次，预读0次，lob逻辑读取0次，lob物理读取0次，lob预读0次。\x0d\x0a\x0d\x0a(1行受影响)\x0d\x0a\x0d\x0a(44行受影响)\x0d\x0a\x0d\x0a表'syscolpars'。扫描计数47，逻辑读取97次，物理读取0次，预读0次，lob逻辑读取0次，lob物理读取0次，lob预读0次。\x0d\x0a\x0d\x0a表'sysschobjs'。扫描计数1，逻辑读取3次，物理读取0次，预读0次，lob逻辑读取0次，lob物理读取0次，lob预读0次。\x0d\x0a\x0d\x0a(1行受影响)\x0d\x0a\x0d\x0asetstatisticsioonselect*fromsyscolumnswhereexists(select1fromsysobjectswhereid=syscolumns.id)select*fromsyscolumnswhereidin(selectidfromsysobjects)setstatisticsiooff\x0d\x0a(419行受影响)\x0d\x0a\x0d\x0a表'syscolpars'。扫描计数1，逻辑读取10次，物理读取0次，预读15次，lob逻辑读取0次，lob物理读取0次，lob预读0次。\x0d\x0a\x0d\x0a表'sysschobjs'。扫描计数1，逻辑读取3次，物理读取0次，预读0次，lob逻辑读取0次，lob物理读取0次，lob预读0次。\x0d\x0a\x0d\x0a(1行受影响)\x0d\x0a\x0d\x0a(419行受影响)\x0d\x0a\x0d\x0a表'syscolpars'。扫描计数1，逻辑读取10次，物理读取0次，预读0次，lob逻辑读取0次，lob物理读取0次，lob预读0次。\x0d\x0a\x0d\x0a表'sysschobjs'。扫描计数1，逻辑读取3次，物理读取0次，预读0次，lob逻辑读取0次，lob物理读取0次，lob预读0次。\x0d\x0a\x0d\x0a(1行受影响)\x0d\x0a\x0d\x0a测试结果(总体来讲exists比in的效率高):\x0d\x0a\x0d\x0a效率:条件因素的索引是非常关键的\x0d\x0a\x0d\x0a把syscolumns作为条件:syscolumns数据大于sysobjects\x0d\x0a\x0d\x0a用in\x0d\x0a\x0d\x0a扫描计数47，逻辑读取97次，\x0d\x0a\x0d\x0a用exists\x0d\x0a\x0d\x0a扫描计数1，逻辑读取3次\x0d\x0a\x0d\x0a把sysobjects作为条件:sysobjects的数据少于syscolumns\x0d\x0a\x0d\x0aexists比in多预读15次