`
步青龙
  • 浏览: 297403 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
博客专栏
72ba33fb-eefe-3de1-bd65-82a6e579265d
Java面试
浏览量:0
社区版块
存档分类
最新评论

in or exits

 
阅读更多

1 in 和 exits存在的原因

   不是所有的查询都有关联

2 in 和 exits并存的原因

   in 和 exits 有各自的优势

3 in 和 exits的原理

   in  先执行子查询 然后执行主表查询,exits先执行主表查询,后执行子查询过滤

4 何时使用in

   过滤性不强,主表不是大表

   例:

 

select * from dep d where d.parent_id in (  select s.dep_id from student  s where s.stu_name like '%小明%'
)
 

5 何时使用exits

   过滤性很强,主表可以是大表(主要靠过滤)
  例:

select * from dep d where d .id = 101 and exits (select 1 from student s where s.stu_name like '%小明%')
 

6 in 和 exits 对比

   适合用in的 查询相关分类

       select * from category c where c.parent_id in(select i.category_id from info i where info.name like '%中国%' ) 

       要比exits有优势

   适合用exits的某时间段的相关分类

     select * from category  createDate< to_date('2012-09-29 10:00:00','yyyy-mm-dd hh24:mi:ss')

and createDate< to_date('2012-10-01 00:00:00','yyyy-mm-dd hh24:mi:ss')

and exits (select i.category_id from info i where info.name like '%中国%' )  

      要比in有优势

7 总结in与exits

   如果主表是大表 不首先考虑exits,但是如果过滤性很强要考虑exits.

   如果主表是小表,两种差不了多远。因为有时候exits的优势是在过滤上 如果主表本来数据很少过滤的影响不大。

 

 

分享到:
评论
3 楼 步青龙 2012-10-11  

大漠老仙 法力无边 神通广大 法驾中原
2 楼 步青龙 2012-10-10  
damoqiongqiu 写道
我可以D一下子吗?
为啥不贴一下查询计划?

欢迎大师,有什么错误之处请大师多多指导,谢谢!!!
1 楼 damoqiongqiu 2012-10-10  
我可以D一下子吗?
为啥不贴一下查询计划?

相关推荐

    Determining the stack usage of applications.pdf

    Stacks are memory regions where data is added or removed in a last-in-first-out (LIFO) manner. In an RTOS, each thread has a separate memory region for its stack. During function execution, data may ...

    无线自动识别系统电路的毕业设计.doc

    When a boat approaches or exits a harbor, the host device sends an inquiry signal. Upon receiving this signal, the onboard sub-device automatically responds with its own signal. The host then decodes ...

    2009 达内Unix学习笔记

    集合了 所有的 Unix命令大全 ...telnet 192.168.0.23 自己帐号 sd08077-you0 ftp工具 192.168.0.202 tools-toolss ... 各个 shell 可互相切换 ksh:$ sh:$ csh:guangzhou% bash:bash-3.00$ ... 命令和参数之间必需用空格隔...

    myBase Desktop 6.3.7

    s launched and test if the opened document has changed or the process has exited then it can automatically update the attachment if changed within the database However communicating with MS Word Excel...

    经典SQL语句

    在某些情况下,`IN`列表可以被`OR`条件表达式替代,例如`SELECT name FROM student WHERE name IN ('zhang', 'wang', 'li', 'zhao')`与`SELECT name FROM student WHERE name = 'zhang' OR name = 'li' OR name = '...

    esp-idf-v3.2.zip

    Enter key exits the help screen. Use Space key, or Y and N keys to enable (Yes) and disable (No) configuration items with checkboxes "[*]" Pressing ? while highlighting a configuration item displays ...

    优秀资料(2021-2022年收藏)消防安全常识二十条附英文翻译.doc

    Be familiar with the location of emergency exits and evacuation routes in your residence, and understand fire prevention and emergency evacuation plans.十七、 定期进行家庭消防安全检查,查看电器线路...

    Turbo C++ 3.0[DISK]

    programs and manuals, read this file in its entirety. TABLE OF CONTENTS ----------------- 1. How to Get Help 2. Installation 3. Features 4. Important Information 5. Testing Your Expanded Memory 6. ...

    Turbo C++ 3.00[DISK]

    programs and manuals, read this file in its entirety. TABLE OF CONTENTS ----------------- 1. How to Get Help 2. Installation 3. Features 4. Important Information 5. Testing Your Expanded Memory 6. ...

    hex2bin_2.2_XiaZaiBa.zip

    -b Batch mode: exits if specified file doesn't exist -c Enable record checksum verification -C [Poly][Init][RefIn][RefOut][XorOut] CRC parameters -e [ext] Output filename extension (without ...

    Multi-agent based modeling and simulating for evacuation process in stadium

    A multi-agent evacuation model is proposed in this paper to simulate the pedestrian evacuation process in stadium with or without obstacles. The authors give a multi-agent individual decision-making ...

    BADI及UserEXIT查找工具

    它首先提示用户输入想要搜索的事务代码,然后通过查询系统表来确定该事务代码下哪些标准SAP UserExits和BADI可用。程序还提供了选项来区分搜索结果,即选择查找UserExit还是BADI。 #### 结论 理解和掌握BADI与User...

    8-07-14_MegaCLI for linux_windows

    SCGCQ00343310 Defect MegaCLI "adpBIOS dsply" command does not display correct information when set to BE or SOE SCGCQ00344574 Defect MegaCLI does not prompt user to reboot after update firmware SCGCQ...

    在线投票:在连接到Firebase的vuejs中进行的在线投票

    1. enter email for authenticated if exits in database then go to voting page otherwise error page. 2. voting page shows your data in database and option to select your candidate. 3. after submit page ...

    wgsd CoreDNS

    wgsd is a CoreDNS plugin that serves WireGuard peer information via DNS-SD semantics.wgsd-client is responsible for keeping peer ... It checks all peers once in a serialized fashion and then exits.

    droplol::mobile_phone_with_arrow:drop.lol CLI客户端,可轻松进行对等文件传输

    the file is sent and then the program exits.When no file is provided, the program will receive all files andsave them in the current directory.全局安装( npm install -g droplol )时,该命令可以作为...

    txtq:用于进程间通信的小消息队列

    path # In real life, temp files go away when the session exits, so be careful. # &gt; [1] "/var/folders/k3/q1f45fsn4_13jbn0742d4zj40000gn/T//RtmpezZ1r3/file6f34c3ca3b4" q &lt;- txtq( path ) # Create a ...

    nao机器人java语音源码

    /// Exits and unregisters the module. /// public void exit() { Variant result = call("exit" ); // no return value } /// /// Outputs the languages installed on the system. /// /// ...

    docker-plantuml-server:https

    用法 # either run in foreground (ctrl-c exits the server)docker run -it -p 8080:8080 --rm neam/plantuml-server# or in background (server keeps running until specifically killed)docker logs -f $( ...

    webcamRecorder

    #change values in config.yml, especially video folder. npm install node index #if script sometimes exits but can last at least an hour, please use #https://github.com/foreverjs/forever or similar tool...

Global site tag (gtag.js) - Google Analytics