in和exists
in 是把外表和内表作hash 连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。
如果查询的两个表大小相当,那么用in和exists差别不大。
全文:
in和exists
in 是把外表和内表作hash 连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。
如果查询的两个表大小相当,那么用in和exists差别不大。
如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in:
例如:表A(小表),表B(大表)1:select * from A where cc in (select cc from B)
效率低,用到了A表上cc列的索引;select * from A where exists(select cc from B where cc=A.cc)
效率高,用到了B表上cc列的索引。
相反的2:select * from B where cc in (select cc from A)
效率高,用到了B表上cc列的索引;select * from B where exists(select cc from A where cc=B.cc)
效率低,用到了A表上cc列的索引。
not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;而not extsts 的子查询依然能用到表上的索引。所以无论那个表大,用not exists都比not in要快。
in 与 =的区别
select name from student where name in ('zhang','wang','li','zhao');
与
select name from student where name='zhang' or name='li' or name='wang' or name='zhao'
的结果是相同的。
参考链接
http://topic.csdn.net/u/20090715/10/ec21e6cc-7265-4c44-a35c-8a0003e73978.html
分享到:
相关推荐
log4j-99.0-does-not-exist.jar 处理logback和log4j的包冲突
前端开源库-check-files-exist检查文件是否存在,检查给定的文件选择器数组或单个选择器是否解析为文件。
IN 和 EXIST的区别
sql语句中in与exist_not_in与not_exist_的区别
commons-logging-1.2.1.1.jar
Laravel开发-laravel-exist-db-client 为Laravel5的exist db提取XML RPC调用的客户端
Not-exist-in-douban 如何贡献 请提交到 index.md 文件
gulp存在的例子gulp-exist能实现什么? 尝试找出答案。 一个完全无蚂蚁的existdb应用程序构建。安装npx degit line-o/gulp-exist-example#main my-new-app你可以做的事假设您使用默认的admin帐户在localhost:8080上...
为病人创建了Android应用程序,他们可以根据可用的时隙使用应用程序预订预约,医生可以根据优先级批准或拒绝预约,医生也可以提供处方,病人可以查看他们的Prescrip…。
exist-ws-commons-util-1.0.2.jar,base64 加密解密 在 Java 中把 .NET 使用 Convert.ToBase64String 转换出的 Base64 还原
Blockchain Basics: A Non-Technical Introduction in 25 Steps By 作者: Daniel Drescher ISBN-10 书号: 1484226038 ISBN-13 书号: 9781484226032 Edition 版本: 1st ed. 出版日期: 2017-03-16 pages 页数: (276 ) ...
ERROR Error: [@ant-design/icons-angular]: the icon redo-o does not exist or is not registered. at IconNotFoundError (ant-design-icons-angular.js:159) at MapSubscriber.project (ant-design-icons-angular...
这个宠物小精灵不存在
开发项目必备的环境变量配置,其中包含java/maven/tomcat环境变量配置
eXist-db XML原生数据库 XML数据库
Laravel开发-exist-db-rest-client 用于通过RESTAPI查询和转换现有数据库结果的Laravel客户端
that exist around us and then make the movement required to fixate the eye accordingly. Our brain, gathers that series of images, perceives the scene and processes the information obtained.
Data clustering is a highly interdisciplinary field, the goal of which is to divide a set of objects into homogeneous groups such that objects in the same group are similar and objects in different ...
Big data burst upon the scene in the first decade of the 21st century, and the first organizations to embrace it were online and startup firms. Arguably, firms like Google, eBay, LinkedIn, and ...
This version can co exist with Fast Report FMX version at the same time. + published "Quality" property of TfrxPDFExport object + published "UseMAPI" property of TfrxExportMail object + published ...