1.先看下示例表:ORACLE中,scott下的两张示例表
- CREATE TABLE EMP
- (
- EMPNO NUMBER(4),
- ENAME VARCHAR2(10 BYTE),
- JOB VARCHAR2(9 BYTE),
- MGR NUMBER(4),
- HIREDATE DATE,
- SAL NUMBER(7,2),
- COMM NUMBER(7,2),
- DEPTNO NUMBER(2)
- )
- CREATE TABLE DEPT
- (
- DEPTNO NUMBER(2),
- DNAME VARCHAR2(14 BYTE),
- LOC VARCHAR2(13 BYTE)
- )
CREATE TABLE EMP
(
EMPNO NUMBER(4),
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
CREATE TABLE DEPT
(
DEPTNO NUMBER(2),
DNAME VARCHAR2(14 BYTE),
LOC VARCHAR2(13 BYTE)
)
2.几条SQL引出的问题:(过滤条件在左表emp 中,sal != 3000)
- --SQL1:全部当成join的条件
- select e.* ,d.deptno , d.dname from emp e left join dept d
- on e.deptno=d.deptno and sal != 3000 order by empno;
- --SQL2:先过滤再join
- select e.*,d.deptno ,d.dname from (select * from emp where sal != 3000 ) e left join dept d
- on e.deptno=d.deptno order by empno;
- --SQL3:先过滤再join
- select e.* ,d.deptno , d.dname from emp e left join dept d
- on e.deptno=d.deptno where sal != 3000 order by empno;
--SQL1:全部当成join的条件
select e.* ,d.deptno , d.dname from emp e left join dept d
on e.deptno=d.deptno and sal != 3000 order by empno;
--SQL2:先过滤再join
select e.*,d.deptno ,d.dname from (select * from emp where sal != 3000 ) e left join dept d
on e.deptno=d.deptno order by empno;
--SQL3:先过滤再join
select e.* ,d.deptno , d.dname from emp e left join dept d
on e.deptno=d.deptno where sal != 3000 order by empno;
SQL1生成的结果:
SQL2和SQL3生成的结果一致:
SQL2或者3的执行计划是一致的:
- 执行计划
- ----------------------------------------------------------
- Plan hash value: 1901738359
- ----------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ----------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 12 | 600 | 8 (25)| 00:00:01 |
- | 1 | SORT ORDER BY | | 12 | 600 | 8 (25)| 00:00:01 |
- |* 2 | HASH JOIN OUTER | | 12 | 600 | 7 (15)| 00:00:01 |
- |* 3 | TABLE ACCESS FULL| EMP | 12 | 444 | 3 (0)| 00:00:01 |
- | 4 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
- ----------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - access("EMP"."DEPTNO"="D"."DEPTNO"(+))
- 3 - filter("SAL"<>3000)
执行计划
----------------------------------------------------------
Plan hash value: 1901738359
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 600 | 8 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 12 | 600 | 8 (25)| 00:00:01 |
|* 2 | HASH JOIN OUTER | | 12 | 600 | 7 (15)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 12 | 444 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMP"."DEPTNO"="D"."DEPTNO"(+))
3 - filter("SAL"<>3000)
可见是先过滤sal!=3000,再join
而SQL1会把整个e.deptno=d.deptno and sal != 3000 当成join条件,一旦满足sal=3000 就会找不到对应的dept.
所以凡遇见join后加的是左边表的过滤
- select e.* ,d.deptno , d.dname from emp e left join dept d
- on e.deptno=d.deptno where sal != 3000 order by empno;
select e.* ,d.deptno , d.dname from emp e left join dept d
on e.deptno=d.deptno where sal != 3000 order by empno;
按照执行计划就可以理解成:
- --先过滤再join
- select e.*,d.deptno ,d.dname from (select * from emp where sal != 3000 ) e left join dept d
- on e.deptno=d.deptno order by empno;
--先过滤再join
select e.*,d.deptno ,d.dname from (select * from emp where sal != 3000 ) e left join dept d
on e.deptno=d.deptno order by empno;
所以,对于过滤的是左边表emp的条件sal!=3000,若是将过滤条件放在join条件之中,例如SQL1,会把它当成整个JOIN的条件,若是将过滤条件当成where放在join后,例如SQL3,就是先过滤再join,结果自然就不一样了。
3.还是几条SQL引出的问题:(过滤条件在右表 dept中,d.loc!='NEW YORK')
- --SQL 4
- select e.* , d.loc from emp e left join dept d on e.deptno=d.deptno and d.loc!='NEW YORK' order by empno;
- --SQL 5
- select e.*, d.loc from emp e left join (select * from dept where dept.loc!='NEW YORK' ) d on e.deptno=d.deptno order by empno;
- --SQL 6
- select e.* , d.loc from emp e left join dept d on e.deptno=d.deptno where d.loc!='NEW YORK' order by empno;
--SQL 4
select e.* , d.loc from emp e left join dept d on e.deptno=d.deptno and d.loc!='NEW YORK' order by empno;
--SQL 5
select e.*, d.loc from emp e left join (select * from dept where dept.loc!='NEW YORK' ) d on e.deptno=d.deptno order by empno;
--SQL 6
select e.* , d.loc from emp e left join dept d on e.deptno=d.deptno where d.loc!='NEW YORK' order by empno;
SQL4的执行计划:
- 执行计划
- ----------------------------------------------------------
- Plan hash value: 1901738359
- ----------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ----------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 14 | 672 | 8 (25)| 00:00:01 |
- | 1 | SORT ORDER BY | | 14 | 672 | 8 (25)| 00:00:01 |
- |* 2 | HASH JOIN OUTER | | 14 | 672 | 7 (15)| 00:00:01 |
- | 3 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
- |* 4 | TABLE ACCESS FULL| DEPT | 3 | 33 | 3 (0)| 00:00:01 |
- ----------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - access("E"."DEPTNO"="D"."DEPTNO"(+))
- 4 - filter("D"."LOC"(+)<>'NEW YORK')
执行计划
----------------------------------------------------------
Plan hash value: 1901738359
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 672 | 8 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 14 | 672 | 8 (25)| 00:00:01 |
|* 2 | HASH JOIN OUTER | | 14 | 672 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| DEPT | 3 | 33 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E"."DEPTNO"="D"."DEPTNO"(+))
4 - filter("D"."LOC"(+)<>'NEW YORK')
可见是先filter("D"."LOC"(+)<>'NEW YORK'),再join。相当于SQL5
所以SQL4和SQL5的执行结果是一样的。
但是SQL6的结果就很戏剧了,SQL6执行结果
先join再过滤:
select * from (select e.* ,d.loc from emp e left join dept d on e.deptno=d.deptno) where loc!='NEW YORK' order by empno
4.更经典的(过滤两张表共同的列):
- select e.* ,d.deptno, d.dname from emp e left join dept d on e.deptno=d.deptno where d.deptno!=30 order by empno;
select e.* ,d.deptno, d.dname from emp e left join dept d on e.deptno=d.deptno where d.deptno!=30 order by empno;
- 执行计划
- ----------------------------------------------------------
- Plan hash value: 3357797783
- ----------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ----------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 9 | 450 | 8 (25)| 00:00:01 |
- | 1 | SORT ORDER BY | | 9 | 450 | 8 (25)| 00:00:01 |
- |* 2 | HASH JOIN | | 9 | 450 | 7 (15)| 00:00:01 |
- |* 3 | TABLE ACCESS FULL| DEPT | 3 | 39 | 3 (0)| 00:00:01 |
- |* 4 | TABLE ACCESS FULL| EMP | 9 | 333 | 3 (0)| 00:00:01 |
- ----------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - access("E"."DEPTNO"="D"."DEPTNO")
- 3 - filter("D"."DEPTNO"<>30)
- 4 - filter("E"."DEPTNO"<>30)
执行计划
----------------------------------------------------------
Plan hash value: 3357797783
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 450 | 8 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 9 | 450 | 8 (25)| 00:00:01 |
|* 2 | HASH JOIN | | 9 | 450 | 7 (15)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| DEPT | 3 | 39 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMP | 9 | 333 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E"."DEPTNO"="D"."DEPTNO")
3 - filter("D"."DEPTNO"<>30)
4 - filter("E"."DEPTNO"<>30)
此时2张表都被过滤后( 3 - filter("D"."DEPTNO"<>30) 4 - filter("E"."DEPTNO"<>30))再JOIN。
查询结果:
呵呵,小心你的where和join吧,先想清楚业务需求,再想清楚SQL原理,才能查出正确的结果,否则就悲剧了。
附件scott.rar是导出的emp和dept的备份。oracle导出版本是 Release 10.2.0.1.0,低于这个版本的,将无法导入我导出的这个文件。
附SQL:
- --全部当成join的条件
- select e.* ,d.deptno , d.dname from emp e left join dept d
- on e.deptno=d.deptno and sal != 3000 order by empno;
- --先过滤再join
- select e.*,d.deptno ,d.dname from (select * from emp where sal != 3000 ) e left join dept d
- on e.deptno=d.deptno order by empno;
- --先过滤再join
- select e.* ,d.deptno , d.dname from emp e left join dept d
- on e.deptno=d.deptno where sal != 3000 order by empno;
- --SQL 4
- select e.* , d.loc from emp e left join dept d on e.deptno=d.deptno and d.loc!='NEW YORK' order by empno;
- --SQL 5
- select e.*, d.loc from emp e left join (select * from dept where dept.loc!='NEW YORK' ) d on e.deptno=d.deptno order by empno;
- --SQL 6
- select e.* ,d.loc from emp e left join dept d on e.deptno=d.deptno where d.loc!='NEW YORK' order by empno;
- select * from (select e.* ,d.loc from emp e left join dept d on e.deptno=d.deptno) where loc!='NEW YORK' order by empno
- select e.* , d.deptno,d.dname from emp e left join dept d on e.deptno=d.deptno and d.deptno!=30 order by empno;
- --先过滤再join
- select e.*,d.deptno,d.dname from emp e left join (select * from dept where dept.deptno!=30 ) d on e.deptno=d.deptno order by empno;
- select e.* ,d.deptno, d.dname from emp e left join dept d on e.deptno=d.deptno where d.deptno!=30 order by empno;
--全部当成join的条件
select e.* ,d.deptno , d.dname from emp e left join dept d
on e.deptno=d.deptno and sal != 3000 order by empno;
--先过滤再join
select e.*,d.deptno ,d.dname from (select * from emp where sal != 3000 ) e left join dept d
on e.deptno=d.deptno order by empno;
--先过滤再join
select e.* ,d.deptno , d.dname from emp e left join dept d
on e.deptno=d.deptno where sal != 3000 order by empno;
--SQL 4
select e.* , d.loc from emp e left join dept d on e.deptno=d.deptno and d.loc!='NEW YORK' order by empno;
--SQL 5
select e.*, d.loc from emp e left join (select * from dept where dept.loc!='NEW YORK' ) d on e.deptno=d.deptno order by empno;
--SQL 6
select e.* ,d.loc from emp e left join dept d on e.deptno=d.deptno where d.loc!='NEW YORK' order by empno;
select * from (select e.* ,d.loc from emp e left join dept d on e.deptno=d.deptno) where loc!='NEW YORK' order by empno
select e.* , d.deptno,d.dname from emp e left join dept d on e.deptno=d.deptno and d.deptno!=30 order by empno;
--先过滤再join
select e.*,d.deptno,d.dname from emp e left join (select * from dept where dept.deptno!=30 ) d on e.deptno=d.deptno order by empno;
select e.* ,d.deptno, d.dname from emp e left join dept d on e.deptno=d.deptno where d.deptno!=30 order by empno;