connect by与where条件执行顺序

张兴康

--无查询条件
select rownum,ename,sys_connect_by_path(ename,'/') from scott.emp
start with mgr is null
connect by prior empno=mgr;
ROWNUM    ENAME    SYS_CONNECT_BY_PATH(ENAME,'/')
1    KING    /KING
2    JONES    /KING/JONES
3    SCOTT    /KING/JONES/SCOTT
4    ADAMS    /KING/JONES/SCOTT/ADAMS
5    FORD    /KING/JONES/FORD
6    SMITH    /KING/JONES/FORD/SMITH
7    BLAKE    /KING/BLAKE
8    ALLEN    /KING/BLAKE/ALLEN
9    WARD    /KING/BLAKE/WARD
10    MARTIN    /KING/BLAKE/MARTIN
11    TURNER    /KING/BLAKE/TURNER
12    JAMES    /KING/BLAKE/JAMES
13    CLARK    /KING/CLARK
14    MILLER    /KING/CLARK/MILLER

--查询条件在connect by后执行
select rownum,ename,sys_connect_by_path(ename,'/') from scott.emp
where ename<>'KING'
start with mgr is null
connect by prior empno=mgr;
ROWNUM    ENAME    SYS_CONNECT_BY_PATH(ENAME,'/')
1    JONES    /KING/JONES
2    SCOTT    /KING/JONES/SCOTT
3    ADAMS    /KING/JONES/SCOTT/ADAMS
4    FORD    /KING/JONES/FORD
5    SMITH    /KING/JONES/FORD/SMITH
6    BLAKE    /KING/BLAKE
7    ALLEN    /KING/BLAKE/ALLEN
8    WARD    /KING/BLAKE/WARD
9    MARTIN    /KING/BLAKE/MARTIN
10    TURNER    /KING/BLAKE/TURNER
11    JAMES    /KING/BLAKE/JAMES
12    CLARK    /KING/CLARK
13    MILLER    /KING/CLARK/MILLER

--connect by中的条件在除过第一行外执行
select rownum,ename,sys_connect_by_path(ename,'/') from scott.emp
start with mgr is null
connect by prior empno=mgr and ename<>'KING';
ROWNUM    ENAME    SYS_CONNECT_BY_PATH(ENAME,'/')
1    KING    /KING
2    JONES    /KING/JONES
3    SCOTT    /KING/JONES/SCOTT
4    ADAMS    /KING/JONES/SCOTT/ADAMS
5    FORD    /KING/JONES/FORD
6    SMITH    /KING/JONES/FORD/SMITH
7    BLAKE    /KING/BLAKE
8    ALLEN    /KING/BLAKE/ALLEN
9    WARD    /KING/BLAKE/WARD
10    MARTIN    /KING/BLAKE/MARTIN
11    TURNER    /KING/BLAKE/TURNER
12    JAMES    /KING/BLAKE/JAMES
13    CLARK    /KING/CLARK
14    MILLER    /KING/CLARK/MILLER

--connect by中的条件在除过第一行外执行，可以过滤掉一个分支
select rownum,ename,sys_connect_by_path(ename,'/') from scott.emp
start with mgr is null
connect by prior empno=mgr and ename<>'JONES';
ROWNUM    ENAME    SYS_CONNECT_BY_PATH(ENAME,'/')
1    KING    /KING
2    BLAKE    /KING/BLAKE
3    ALLEN    /KING/BLAKE/ALLEN
4    WARD    /KING/BLAKE/WARD
5    MARTIN    /KING/BLAKE/MARTIN
6    TURNER    /KING/BLAKE/TURNER
7    JAMES    /KING/BLAKE/JAMES
8    CLARK    /KING/CLARK
9    MILLER    /KING/CLARK/MILLER

--查询条件放入子查询，效果与将查询条件放在connect by中（除了第一行外）
select rownum, ename, sys_connect_by_path(ename, '/')
  from (select * from scott.emp where ename <> 'KING')
start with mgr is null
connect by prior empno = mgr;

no rows selected

--查询条件放入子查询，效果与将查询条件放在connect by中（除了第一行外）
select rownum, ename, sys_connect_by_path(ename, '/')
  from (select * from scott.emp where ename <> 'JONES')
start with mgr is null
connect by prior empno = mgr;
ROWNUM    ENAME    SYS_CONNECT_BY_PATH(ENAME,'/')
1    KING    /KING
2    BLAKE    /KING/BLAKE
3    ALLEN    /KING/BLAKE/ALLEN
4    WARD    /KING/BLAKE/WARD
5    MARTIN    /KING/BLAKE/MARTIN
6    TURNER    /KING/BLAKE/TURNER
7    JAMES    /KING/BLAKE/JAMES
8    CLARK    /KING/CLARK
9    MILLER    /KING/CLARK/MILLER

张兴康