- inner join 时的on与where一样，把限制条件放在哪里都一样
- on是结合时的条件，以什么样的条件来结合，或者说什么样的数据才可以结合
- where是筛选条件，满足什么条件的数据才可以查询出来
- on筛选时Null值可以查询出来，而where则会去掉null值，所以两者得到的结果集不总是一样的
- inner join
-- inner join 时的on
scott@XE> select * from emp e inner join dept d on (e.ename='ALLEN' and e.deptno=d.deptno);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME
---------- ---------- --------- ---------- -------- ---------- ---------- ---------- ---------- --------------
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30 30 SALES
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 351108634
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 117 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 117 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | EMP | 1 | 87 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 30 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("E"."ENAME"='ALLEN')
4 - access("E"."DEPTNO"="D"."DEPTNO")
-- inner join 时的where
select * from emp e inner join dept d on (e.deptno=d.deptno) where e.ename='ALLEN';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME
---------- ---------- --------- ---------- -------- ---------- ---------- ---------- ---------- --------------
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30 30 SALES
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 351108634
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 117 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 117 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | EMP | 1 | 87 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 30 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("E"."ENAME"='ALLEN')
4 - access("E"."DEPTNO"="D"."DEPTNO")
- left join/right join
-- 两表通过deptno的结合结果，emp表数据全部被查询出来
scott@XE> select * from emp e left join dept d on (e.deptno=d.deptno);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME
---------- ---------- --------- ---------- -------- ---------- ---------- ---------- ---------- --------------
7934 MILLER CLERK 7782 82-01-23 1300 10 10 ACCOUNTING
7839 KING PRESIDENT 81-11-17 5000 10 10 ACCOUNTING
7782 CLARK MANAGER 7839 81-06-09 2450 10 10 ACCOUNTING
7902 FORD ANALYST 7566 81-12-03 3000 20 20 RESEARCH
7876 ADAMS CLERK 7788 87-05-23 1100 20 20 RESEARCH
7788 SCOTT ANALYST 7566 87-04-19 3000 20 20 RESEARCH
7566 JONES MANAGER 7839 81-04-02 2975 20 20 RESEARCH
7369 SMITH CLERK 7902 80-12-17 800 20 20 RESEARCH
7900 JAMES CLERK 7698 81-12-03 950 30 30 SALES
7844 TURNER SALESMAN 7698 81-09-08 1500 0 30 30 SALES
7698 BLAKE MANAGER 7839 81-05-01 2850 30 30 SALES
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30 30 SALES
7521 WARD SALESMAN 7698 81-02-22 1250 500 30 30 SALES
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30 30 SALES
14 rows selected.
-- on是结合时的筛选条件，不符合d.dname='SALES'的记录以null填充
scott@XE> select * from emp e left join dept d on (e.deptno=d.deptno and d.dname='SALES');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME
---------- ---------- --------- ---------- -------- ---------- ---------- ---------- ---------- --------------
7900 JAMES CLERK 7698 81-12-03 950 30 30 SALES
7844 TURNER SALESMAN 7698 81-09-08 1500 0 30 30 SALES
7698 BLAKE MANAGER 7839 81-05-01 2850 30 30 SALES
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30 30 SALES
7521 WARD SALESMAN 7698 81-02-22 1250 500 30 30 SALES
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30 30 SALES
7934 MILLER CLERK 7782 82-01-23 1300 10
7839 KING PRESIDENT 81-11-17 5000 10
7782 CLARK MANAGER 7839 81-06-09 2450 10
7902 FORD ANALYST 7566 81-12-03 3000 20
7876 ADAMS CLERK 7788 87-05-23 1100 20
7788 SCOTT ANALYST 7566 87-04-19 3000 20
7566 JONES MANAGER 7839 81-04-02 2975 20
7369 SMITH CLERK 7902 80-12-17 800 20
14 rows selected.
-- on是结合时的筛选条件，不符合e.ename='ALLEN'的记录以null填充
scott@XE> select * from emp e left join dept d on (e.deptno=d.deptno and e.ename='ALLEN');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME
---------- ---------- --------- ---------- -------- ---------- ---------- ---------- ---------- --------------
7369 SMITH CLERK 7902 80-12-17 800 20
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30 30 SALES
7521 WARD SALESMAN 7698 81-02-22 1250 500 30
7566 JONES MANAGER 7839 81-04-02 2975 20
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 81-05-01 2850 30
7782 CLARK MANAGER 7839 81-06-09 2450 10
7788 SCOTT ANALYST 7566 87-04-19 3000 20
7839 KING PRESIDENT 81-11-17 5000 10
7844 TURNER SALESMAN 7698 81-09-08 1500 0 30
7876 ADAMS CLERK 7788 87-05-23 1100 20
7900 JAMES CLERK 7698 81-12-03 950 30
7902 FORD ANALYST 7566 81-12-03 3000 20
7934 MILLER CLERK 7782 82-01-23 1300 10
14 rows selected.
-- where是结合后的筛选条件，不符合d.dname='SALES'的记录被去掉
scott@XE> select * from emp e left join dept d on (e.deptno=d.deptno) where d.dname='SALES';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME
---------- ---------- --------- ---------- -------- ---------- ---------- ---------- ---------- --------------
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30 30 SALES
7521 WARD SALESMAN 7698 81-02-22 1250 500 30 30 SALES
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30 30 SALES
7698 BLAKE MANAGER 7839 81-05-01 2850 30 30 SALES
7844 TURNER SALESMAN 7698 81-09-08 1500 0 30 30 SALES
7900 JAMES CLERK 7698 81-12-03 950 30 30 SALES
6 rows selected.
-- where是结合后的筛选条件，不符合e.ename='ALLEN'的记录被去掉
scott@XE> select * from emp e left join dept d on (e.deptno=d.deptno) where e.ename='ALLEN';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME
---------- ---------- --------- ---------- -------- ---------- ---------- ---------- ---------- --------------
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30 30 SALES
1 row selected.
0 件のコメント:
コメントを投稿