网站建设方案,原材料价格查询网站,网站建设技术手段,通讯员队伍建设与网站新闻管理系列文章目录
【SQL开发实战技巧】系列#xff08;一#xff09;:关于SQL不得不说的那些事 【SQL开发实战技巧】系列#xff08;二#xff09;#xff1a;简单单表查询 【SQL开发实战技巧】系列#xff08;三#xff09;#xff1a;SQL排序的那些事 【SQL开发实战技巧…系列文章目录
【SQL开发实战技巧】系列一:关于SQL不得不说的那些事 【SQL开发实战技巧】系列二简单单表查询 【SQL开发实战技巧】系列三SQL排序的那些事 【SQL开发实战技巧】系列四从执行计划讨论UNION ALL与空字符串UNION与OR的使用注意事项 【SQL开发实战技巧】系列五从执行计划看IN、EXISTS 和 INNER JOIN效率我们要分场景不要死记网上结论 【SQL开发实战技巧】系列六从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率记住内外关联条件不要乱放 【SQL开发实战技巧】系列七从有重复数据前提下如何比较出两个表中的差异数据及对应条数聊起 【SQL开发实战技巧】系列八聊聊如何插入数据时比约束更灵活的限制数据插入以及怎么一个insert语句同时插入多张表 【SQL开发实战技巧】系列九一个update误把其他列数据更新成空了Merger改写update给你五种删除重复数据的写法 文章目录系列文章目录前言一、从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率二、外连接中的条件不要乱放建议大家使用join而非()总结前言
本篇文章讲解的主要内容是从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率还是那就话别死记网上结论、在使用内外关联时特别是简写方式时记住关联条件不要乱放 【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作毕竟SQL开发在数据分析场景非常重要且基础面试也会经常问SQL开发和调优经验相信当我写完这一系列文章也能再有所收获未来面对SQL面试也能游刃有余~。 一、从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率
有些单位的部门如40)中一个员工也没有只是设了一个部门名字如下列语句
select count(*) from dept where deptno40;如何通过关联查询把这些信息查出来 同样有三种写法:NOT IN、NOT EXISTS 和LEFT JOIN。 语句及PLAN如下版本为11.2.0.4.0 )。 环境
alter table dept add constraints pk_dept primary key (deptno); --如果你有就不用建了NOT IN用法
EXPLAIN PLAN FOR select *
FROM dept
WHERE deptno NOT IN (SELECT emp.deptno FROM emp WHERE emp.deptno IS NOT NULL);
SELECT * FROM TABLE(dbms_xplan.display());PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1353548327
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 6 (17)| 00
| 1 | MERGE JOIN ANTI | | 1 | 23 | 6 (17)| 00
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00
|* 4 | SORT UNIQUE | | 14 | 42 | 4 (25)| 00
|* 5 | TABLE ACCESS FULL | EMP | 14 | 42 | 3 (0)| 00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------4 - access(DEPTNOEMP.DEPTNO)filter(DEPTNOEMP.DEPTNO)5 - filter(EMP.DEPTNO IS NOT NULL)19 rows selectedNOT EXISTS 用法
EXPLAIN PLAN FOR SELECT*
FROM dept
WHERE NOT EXISTS ( SELECT NULL FROM emp WHERE emp.deptno dept.deptno) ;
SELECT * FROM TABLE(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1353548327
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 6 (17)| 00
| 1 | MERGE JOIN ANTI | | 1 | 23 | 6 (17)| 00
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00
|* 4 | SORT UNIQUE | | 14 | 42 | 4 (25)| 00
|* 5 | TABLE ACCESS FULL | EMP | 14 | 42 | 3 (0)| 00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------4 - access(EMP.DEPTNODEPT.DEPTNO)filter(EMP.DEPTNODEPT.DEPTNO)5 - filter(EMP.DEPTNO IS NOT NULL)19 rows selectedLEFT JOIN 用法
根据前面介绍过的左联知识,LEFT JOIN 取出的是左表中所有的数据,其中与右表不匹配的就表示左表NOT IN右表。 所以这里LEFT JOIN加上条件TS NULL,就是LEFT JOIN的写法
EXPLAIN PLAN FOR
SELECT dept.*
FROM dept
LEFT JOIN emp ON emp.deptno dept.deptno WHERE emp.deptno IS NULL;SELECT * FROM TABLE(dbms_xplan.display());PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1353548327
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 6 (17)| 00
| 1 | MERGE JOIN ANTI | | 1 | 23 | 6 (17)| 00
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00
|* 4 | SORT UNIQUE | | 14 | 42 | 4 (25)| 00
|* 5 | TABLE ACCESS FULL | EMP | 14 | 42 | 3 (0)| 00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------4 - access(EMP.DEPTNODEPT.DEPTNO)filter(EMP.DEPTNODEPT.DEPTNO)5 - filter(EMP.DEPTNO IS NOT NULL)19 rows selected通过看上面的执行计划三个SQL用的都是 MERGE JOIN ANTI, 说明这三种方法的效率一样。 如果想改写就要对比改写前后的PLAN,根据PLAN来判断并测试哪种方法的效率高一定要记住不能凭借某些结论来碰运气。
二、外连接中的条件不要乱放建议大家使用join而非()
对于系列三博客介绍的左联语句,见下面的数据。
SELECT l.str AS left_str, r.str AS right_str,r.status FROM l
LEFT JOIN r ON l.v r.v
ORDER BY 1 , 2 ;
LEFT_STR RIGHT_STR STATUS
-------- --------- ----------
left_1
left_2
left_3 right_3 1
left_4 right_4 0那现在有这么一个需求对于其中的L表四条数据都返回。而对于R表我们需要只显示其中的status1的数据也就是下面这样的结果
LEFT_STR RIGHT_STR STATUS
-------- --------- ----------
left_1
left_2
left_3 right_3 1
left_4 对于这个需求可能有些人会加一个where条件然后结果就变成了下面这样了: left join写法
SELECT l.str AS left_str, r.str AS right_str,r.status FROM l
LEFT JOIN r ON (l.v r.v)
where r.status1
ORDER BY 1 , 2;
LEFT_STR RIGHT_STR STATUS
-------- --------- ----------
left_3 right_3 1写法
SELECT l.str AS left_str, r.str AS right_str, r.statusFROM l, rwhere l.v r.v()and r.status 1ORDER BY 1, 2;
LEFT_STR RIGHT_STR STATUS
-------- --------- ----------
left_3 right_3 1而此时的执行计划
SQL EXPLAIN PLAN FOR2 SELECT l.str AS left_str, r.str AS right_str,r.status3 FROM l4 LEFT JOIN r ON (l.v r.v)5 where r.status16 ORDER BY 1 , 2;ExplainedSQL SELECT * FROM TABLE(dbms_xplan.display());PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 688663707
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 42 | 7 (15)| 00:00:01 |
| 1 | SORT ORDER BY | | 2 | 42 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN | | 2 | 42 | 6 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| R | 2 | 24 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| L | 4 | 36 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------2 - access(L.VR.V)3 - filter(R.STATUS1)17 rows selected很明显结果以及执行计划(HASH JOIN)与我们期望得到的结果都不一致这是很多人在写查询或更改查询时常遇到的一种错误。问题就在于所加条件的位置及写法正确的写法分别如下
SQL SELECT l.str AS left_str, r.str AS right_str, r.status2 FROM l3 LEFT JOIN r4 ON (l.v r.v and r.status 1)5 ORDER BY 1, 2;LEFT_STR RIGHT_STR STATUS
-------- --------- ----------
left_1
left_2
left_3 right_3 1
left_4 SQL SELECT l.str AS left_str, r.str AS right_str, r.status2 FROM l, r3 where l.v r.v()4 and r.status() 15 ORDER BY 1, 2;LEFT_STR RIGHT_STR STATUS
-------- --------- ----------
left_1
left_2
left_3 right_3 1
left_4 看一下这时候的执行计划
SQL EXPLAIN PLAN FOR2 SELECT l.str AS left_str, r.str AS right_str, r.status3 FROM l4 LEFT JOIN r5 ON (l.v r.v and r.status 1)6 ORDER BY 1, 2;ExplainedSQL SELECT * FROM TABLE(dbms_xplan.display());PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2310059642
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 84 | 7 (15)| 00:00:01 |
| 1 | SORT ORDER BY | | 4 | 84 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN OUTER | | 4 | 84 | 6 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| L | 4 | 36 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| R | 2 | 24 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------2 - access(L.VR.V())4 - filter(R.STATUS()1)17 rows selected以上两种写法结果均正确且根据执行计划HASH JOIN OUTER明确走的是外连接。而且根据上面查询我们能够看出来JOIN的方式明显更容易辨别这也是我反复建议使用JOIN的原因。 对于上面SQL我们还可以使用先过滤再关联的方式即R表先过滤
(select * from r where status1) r总结
同上一篇博客所说在使用in exists或则NOT IN、NOT EXISTS 和 LEFT JOIN时候不要想当然的认为in和not in效率极其低下在本章案例中通过执行计划能够直观的看到三者效率竟然一致了所以读万卷书不如行万里路网上别人做的总结再好也不如自己实践一把来的真实。还有就是在使用关联查询时候关联条件和过滤条件一定要想好放哪里不然你会想当然的错了
