讨论下这个sql语句

又是违规昵称 2009-03-13 11:10:28
不知道大家有这样用过没,这个sql语句有什么意义?

select * from emp e1 left join emp e2 on e1.empno = e2.empno and e1.deptno = 20;
...全文
259 25 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
25 条回复
切换为时间正序
请发表友善的回复…
发表回复
又是违规昵称 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,OK?

文字好象不太好理解,其实就相当于下面这句:
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)

17,382

社区成员

发帖
与我相关
我的任务
社区描述
Oracle 基础和管理
社区管理员
  • 基础和管理社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧