讨论下这个sql语句

Question

不知道大家有这样用过没，这个sql语句有什么意义?select * from emp e1 left join emp e2 on e1.empno = e2.empno and e1.deptno = 20;

Answer 1

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
没看出玄机，与楼主的猩猩不对应啊

Answer 2

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

Answer 3

select * from emp e1 left join emp e2 on e1.empno = e2.empno and e1.deptno = 20;
这个好像没玄机
等同于把EMP表中，deptno=20的数据横向显示2遍

Answer 4

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

Answer 5

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

Answer 6

一般情况下，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                    1014 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                    3014 rows selected.SQL> 

Answer 7

是这样解读的:
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 

Answer 8

为方便在查询计划里查看出来sql的执行顺序，下面这个例子采用两个不相同的表进行演示．SQL> select * from a;        ID NAME
---------- ----------
         1 aaa
         2 bbb
         3 cccSQL> select * from b;        ID NAME
---------- ----------
         1 b_aaa
         2 b_bbb
         3 b_cccSQL> 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)

Answer 9

先贴两种情况的执行计划：
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 statementSQL> 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

Answer 10

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到底是什么样的？

Answer 11

楼主，cmd登录下 要看执行，要怎么弄，这这个执行计划字体好漂亮 啊，是什么工具啊

Answer 12

从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 statementSQL> 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的

Answer 13

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

Answer 14

下面是我机器上的执行计划 oracle 9i 2Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=820 Card=409 Bytes=25767)
   1    0   NESTED LOOPS (OUTER) (Cost=820 Card=409 Bytes=25767)
   2    1     TABLE ACCESS (FULL) OF 'A' (Cost=2 Card=409 Bytes=17587)
   3    1     VIEW
   4    3       TABLE ACCESS (FULL) OF 'B' (Cost=2 Card=1 Bytes=20)
怎么第二列有2个1呢，
这个执行顺序是什么
借楼主贵地，问个问题

Answer 15

收到，前辈用什么 工具，我的执行计划怎么没有 下面语句
Predicate Information (identified by operation id):
---------------------------------------------------   1 - access("E1"."EMPNO"="E2"."EMPNO"(+))
   3 - filter("E2"."DEPTNO"(+)=20)

Answer 16

to sleepzzzzz 
我也意识到了
已经别外开贴了
http://topic.csdn.net/u/20090313/13/2f08ec84-e479-4e91-8b21-e516a9a8541f.html?seed=349143138