讨论下这个sql语句

2009-03-13 11:10:28 335 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

快速上手CH340N电路设计（CH340N USB转串口模块 USB Type-C接口 CH340系列芯片讲解）一、上模块二、功能分析 l 芯片：CH340N l 输入接口：USB、TYPE-C l 输出接口：TTL(5V\3.3V\GND\TX\RX) l 指示灯：电源、TX、RX 三、CH340x芯片 l CH340 是一个 USB 总线的转接芯片，实现 USB 转串口 l 全速 USB 设备接口，兼容 USB V2.0 l 硬件全双工串口，内置收发缓冲区，支持通讯波特率 50bps～2Mbps l 支持常用的 MODEM 联络信号 RTS、DTR、DCD、RI、DS... 阅读详情

不知道大家有这样用过没，这个sql语句有什么意义?

select * from emp e1 left join emp e2 on e1.empno = e2.empno and e1.deptno = 20;

...全文

【电击防护篇】电击防护技术详解：从人体效应到接地系统全解析本文系统讲解了电击防护的关键知识，包括电流对人体的影响（如30mA是心室颤动阈值）、人体电阻特性（700Ω-6100Ω）以及电气接触防护措施。重点分析了TT、TN和IT三大接地系统的结构特点与应用场景，强调TN-S系统最安全，IT系统供电连续性最佳。详细介绍了漏电保护器（RCD）的工作原理与选型指南，建议人身防护选用30mA高灵敏度RCD。文章通过对比表总结三大系统的安全性、成本及适用场景，为电气安全设计提供实用参考。阅读详情

又是违规昵称

基础和管理 17,377 成员 95,111 内容

+ 关注 + 加入社区

切换为时间正序

又是违规昵称 2009-03-13

打赏
举报

to fuyou001:
在sqlplus下, set autotrace on就可以了啊

又是违规昵称 2009-03-13

打赏
举报

从emp另建了一个t1表，再做：



SQL> select * from emp e1 left join t1 e2 on e1.empno = e2.empno and e1.deptno = 20;



14 rows selected.





Execution Plan

----------------------------------------------------------

Plan hash value: 2147177880



-----------------------------------------------------------------------------

| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |      |    14 |  2436 |    45   (0)| 00:00:01 |

|   1 |  NESTED LOOPS OUTER  |      |    14 |  2436 |    45   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL  | EMP  |    14 |  1218 |     3   (0)| 00:00:01 |

|   3 |   VIEW               |      |     1 |    87 |     3   (0)| 00:00:01 |

|*  4 |    FILTER            |      |       |       |            |          |

|*  5 |     TABLE ACCESS FULL| T1   |     1 |    87 |     3   (0)| 00:00:01 |

-----------------------------------------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



   4 - filter("E1"."DEPTNO"=20)

   5 - filter("E1"."EMPNO"="E2"."EMPNO")



Note

-----

   - dynamic sampling used for this statement



SQL> select * from emp e1 left join t1 e2 on e1.empno = e2.empno and e2.deptno = 20;



14 rows selected.





Execution Plan

----------------------------------------------------------

Plan hash value: 4277803294



---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    14 |  2436 |     7  (15)| 00:00:01 |

|*  1 |  HASH JOIN OUTER   |      |    14 |  2436 |     7  (15)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| EMP  |    14 |  1218 |     3   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| T1   |     5 |   435 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



   1 - access("E1"."EMPNO"="E2"."EMPNO"(+))

   3 - filter("E2"."DEPTNO"(+)=20)



Note

-----

   - dynamic sampling used for this statement

第一个，我指定的是emp.deptno = 20，但是创建的view是基于t1的

大家说说，这两种写法到底是用什么在做nested loop和hash join的

fuyou001 2009-03-13

打赏
举报

[Quote=引用 12 楼 lpc19598188 的回复:]
先贴两种情况的执行计划：

SQL code
SQL> select * from emp e1 left join emp e2 on e1.empno = e2.empno and e2.deptno = 20;

Execution Plan
----------------------------------------------------------
Plan hash value: 2341341676

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------…
[/Quote]
楼主，cmd登录下要看执行，要怎么弄，这这个执行计划字体好漂亮啊，是什么工具啊

又是违规昵称 2009-03-13

打赏
举报

sleepzzzzz说了其中的一种情况哦

这里



|   3 |   VIEW                         |        |     1 |    87 |     2   (0)| 00:00:01 |

|*  4 |    FILTER                      |        |       |       |            |          |

|   5 |     TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    87 |     2   (0)| 00:00:01 |

|*  6 |      INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     1   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

这个view到底是什么样的？

又是违规昵称 2009-03-13

打赏
举报

先贴两种情况的执行计划：



SQL>  select * from emp e1 left join emp e2 on e1.empno = e2.empno and e2.deptno = 20;



Execution Plan

----------------------------------------------------------

Plan hash value: 2341341676



---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    14 |  2436 |     7  (15)| 00:00:01 |

|*  1 |  HASH JOIN OUTER   |      |    14 |  2436 |     7  (15)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| EMP  |    14 |  1218 |     3   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| EMP  |     5 |   435 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



   1 - access("E1"."EMPNO"="E2"."EMPNO"(+))

   3 - filter("E2"."DEPTNO"(+)=20)



Note

-----

   - dynamic sampling used for this statement



SQL> select * from emp e1 left join emp e2 on e1.empno = e2.empno and e1.deptno = 20;



Execution Plan

----------------------------------------------------------

Plan hash value: 3186051241



-----------------------------------------------------------------------------------------

| Id  | Operation                      | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |        |    14 |  2436 |    31   (0)| 00:00:01 |

|   1 |  NESTED LOOPS OUTER            |        |    14 |  2436 |    31   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL            | EMP    |    14 |  1218 |     3   (0)| 00:00:01 |

|   3 |   VIEW                         |        |     1 |    87 |     2   (0)| 00:00:01 |

|*  4 |    FILTER                      |        |       |       |            |          |

|   5 |     TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    87 |     2   (0)| 00:00:01 |

|*  6 |      INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     1   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



   4 - filter("E1"."DEPTNO"=20)

   6 - access("E1"."EMPNO"="E2"."EMPNO")



Note

-----

   - dynamic sampling used for this statement

sleepzzzzz 2009-03-13

打赏
举报

为方便在查询计划里查看出来sql的执行顺序，下面这个例子采用两个不相同的表进行演示．

SQL> select * from a;



        ID NAME

---------- ----------

         1 aaa

         2 bbb

         3 ccc



SQL> select * from b;



        ID NAME

---------- ----------

         1 b_aaa

         2 b_bbb

         3 b_ccc



SQL> set autotrace traceonly

SQL> select * from a left join b on a.id=b.id and a.id = 1;





执行计划

----------------------------------------------------------

Plan hash value: 2608930719



----------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |     3 |   120 |    12   (0)| 00:00:01 |

|   1 |  NESTED LOOPS OUTER |      |     3 |   120 |    12   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL | A    |     3 |    60 |     3   (0)| 00:00:01 | a表后

|   3 |   VIEW              |      |     1 |    20 |     3   (0)| 00:00:01 |

|*  4 |    TABLE ACCESS FULL| B    |     1 |    20 |     3   (0)| 00:00:01 |　b表先运算----------------------------------------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



   4 - filter("A"."ID"=1 AND "B"."ID"=1)

sleepzzzzz 2009-03-13

打赏
举报

是这样解读的:
e1去left join 与e1.deptno = 20对应的e2表中的记录，而不是先过滤e1的值再去left join,ＯＫ？

文字好象不太好理解，其实就相当于下面这句：
select * from emp e1 left join (select * from emp where deptno = 20) e2 on e1.empno = e2.empno

[Quote=引用楼主 lpc19598188 的帖子:]
不知道大家有这样用过没，这个sql语句有什么意义?

select * from emp e1 left join emp e2 on e1.empno = e2.empno and e1.deptno = 20;
[/Quote]

又是违规昵称 2009-03-13

打赏
举报

一般情况下，on后面的连接条件应该是e1.xxx = e2.xxx
但是如果取常量的话，就会是这种情况:



SQL> select * from emp e1 left join emp e2 on e1.empno = e2.empno and e1.deptno = 20; 



     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO      EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO

---------- ---------- --------- ---------- ------------ ---------- ---------- ---------- ---------- ---------- --------- ---------- ------------ ---------- ---------- ----------

      7369 SMITH      CLERK           7902 17-DEC-80           800                    20       7369 SMITH      CLERK           7902 17-DEC-80           800                    20

      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30

      7566 JONES      MANAGER         7839 02-APR-81          2975                    20       7566 JONES      MANAGER         7839 02-APR-81          2975                    20

      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30

      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10

      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20       7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20

      7839 KING       PRESIDENT            17-NOV-81          5000                    10

      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30

      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20       7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20



     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO      EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO

---------- ---------- --------- ---------- ------------ ---------- ---------- ---------- ---------- ---------- --------- ---------- ------------ ---------- ---------- ----------

      7900 JAMES      CLERK           7698 03-DEC-81           950                    30

      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20       7902 FORD       ANALYST         7566 03-DEC-81          3000                    20

      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10



14 rows selected.



SQL> select * from emp e1 left join emp e2 on e1.empno = e2.empno and e2.deptno = 20;



     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO      EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO

---------- ---------- --------- ---------- ------------ ---------- ---------- ---------- ---------- ---------- --------- ---------- ------------ ---------- ---------- ----------

      7369 SMITH      CLERK           7902 17-DEC-80           800                    20       7369 SMITH      CLERK           7902 17-DEC-80           800                    20

      7566 JONES      MANAGER         7839 02-APR-81          2975                    20       7566 JONES      MANAGER         7839 02-APR-81          2975                    20

      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20       7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20

      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20       7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20

      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20       7902 FORD       ANALYST         7566 03-DEC-81          3000                    20

      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30

      7839 KING       PRESIDENT            17-NOV-81          5000                    10

      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10

      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30

      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30



     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO      EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO

---------- ---------- --------- ---------- ------------ ---------- ---------- ---------- ---------- ---------- --------- ---------- ------------ ---------- ---------- ----------

      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30

      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10

      7900 JAMES      CLERK           7698 03-DEC-81           950                    30



14 rows selected.



SQL>

mumu_java 2009-03-13

打赏
举报

lz的语句和select * from emp e1 left outer join emp e2 on e1.empno = e2.empno and e1.deptno = 20;
其实是一样的效果，就是左外连接.具体意义一般应该不会这样用,作为多个表(两个以上)关联查询时这个结果会做为一个中间结果在去和其他表关联就有意义了。

areswang 2009-03-13

打赏
举报

有点意思，关注！

Andy__Huang 2009-03-13

打赏
举报

我认为没有任何意义,left join后面都是多余的代码

yf520gn 2009-03-13

打赏
举报



select * from emp e1 left join emp e2 on e1.empno = e2.empno and e1.deptno = 20;

这个好像没玄机
等同于把EMP表中，deptno=20的数据横向显示2遍

Andy__Huang 2009-03-13

打赏
举报

[Quote=引用楼主 lpc19598188 的帖子:]
不知道大家有这样用过没，这个sql语句有什么意义?

select * from emp e1 left join emp e2 on e1.empno = e2.empno and e1.deptno = 20;
[/Quote]

这个语句是左联,意思是左边全部的,
和下面语句一样
select * from emp

Allan_xd 2009-03-13

打赏
举报

要说意义的话就是表中每个字段都显示了两次

fuyou001 2009-03-13

打赏
举报

期待猩猩们解开玄机

fuyou001 2009-03-13

打赏
举报



select e1.empno,e2.empno, e1.deptno 

  from scott.emp e1

  left join scott.emp e2 on e1.empno = e2.empno

                  and e1.deptno = 20;

1 7521 30
2 7369 7369 20
3 7566 7566 20
4 7499 30
5 7654 30
6 7698 30
7 7782 10
8 7788 7788 20
9 7839 10
10 7844 30
11 7876 7876 20
12 7900 30
13 7902 7902 20
没看出玄机，与楼主的猩猩不对应啊

qwx312347236 2009-03-13

打赏
举报

是个外连接，但没有什么意思

changeking 2009-03-13

打赏
举报

牛人都出来了

fuyou001 2009-03-13

打赏
举报

to sleepzzzzz
[Quote=引用 21 楼 sleepzzzzz 的回复:]
说多了就跑题了，其它的问题开帖讨论并散分啊，哈哈
[/Quote]
我也意识到了
已经别外开贴了
http://topic.csdn.net/u/20090313/13/2f08ec84-e479-4e91-8b21-e516a9a8541f.html?seed=349143138

sleepzzzzz 2009-03-13

打赏
举报

[Quote=引用 20 楼 Allan_xd 的回复:]
到底是不是外连接呀
[/Quote]

是外连接．

加载更多回复（5）

讨论下这个sql语句

相关推荐