树形查询中where的作用

start with定义如下

SELECT  Is the standard SELECT clause
LEVEL For each row returned by a hierarchical query, the LEVEL  pseudocolumn returns 1 for a root row, 2 for a child of a root, and so on.
FROM tableSpecifies the table, view, or snapshot containing the columns. You can  select from only one table.
WHERE Restricts the rows returned by the query without affecting other rows of  the hierarchy.
START WITH Specifies the root rows of the hierarchy (where to start). This clause is required for a true hierarchical query.
CONNECT BY Specifies the columns in which the relationship between parent and  child PRIOR rows exist. This clause is required for a hierarchical query. 

有人对其中的where有疑问,现实验如下

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as gy0202@192.168.253.5/orcl
 
SQL> 
SQL> select empno,ename,mgr
  2  from scott.emp
  3  order by 3 nulls first,1;
 
EMPNO ENAME        MGR
----- ---------- -----
 7839 KING       
 7788 SCOTT       7566
 7902 FORD        7566
 7499 ALLEN       7698
 7521 WARD        7698
 7654 MARTIN      7698
 7844 TURNER      7698
 7900 JAMES       7698
 7934 MILLER      7782
 7876 ADAMS       7788
 7566 JONES       7839
 7698 BLAKE       7839
 7782 CLARK       7839
 7369 SMITH       7902
 
14 rows selected

SQL> select level,lpad(' ',(level-1)*2 + 1,'*') || to_char(empno) as empno,ename,mgr,prior ename as mgr_name
  2    from scott.emp
  3   start with mgr is null
  4  connect by prior empno = mgr;
 
     LEVEL EMPNO             ENAME        MGR MGR_NAME
---------- ----------------- ---------- ----- ----------
         1  7839             KING             
         2 ** 7566           JONES       7839 KING
         3 **** 7788         SCOTT       7566 JONES
         4 ****** 7876       ADAMS       7788 SCOTT
         3 **** 7902         FORD        7566 JONES
         4 ****** 7369       SMITH       7902 FORD
         2 ** 7698           BLAKE       7839 KING
         3 **** 7499         ALLEN       7698 BLAKE
         3 **** 7521         WARD        7698 BLAKE
         3 **** 7654         MARTIN      7698 BLAKE
         3 **** 7844         TURNER      7698 BLAKE
         3 **** 7900         JAMES       7698 BLAKE
         2 ** 7782           CLARK       7839 KING
         3 **** 7934         MILLER      7782 CLARK
 
14 rows selected


SQL> select ename,mgr,prior ename as mgr_name
  2    from scott.emp
  3   where empno != 7698
  4   start with mgr is null
  5  connect by prior empno = mgr;
 
ENAME        MGR MGR_NAME
---------- ----- ----------
KING             
JONES       7839 KING
SCOTT       7566 JONES
ADAMS       7788 SCOTT
FORD        7566 JONES
SMITH       7902 FORD
ALLEN       7698 BLAKE
WARD        7698 BLAKE
MARTIN      7698 BLAKE
TURNER      7698 BLAKE
JAMES       7698 BLAKE
CLARK       7839 KING
MILLER      7782 CLARK
 
13 rows selected

SQL> select empno, ename, mgr, prior ename as mgr_name
  2    from (select * from scott.emp where empno != 7698) emp
  3   start with mgr is null
  4  connect by prior empno = mgr;
 
EMPNO ENAME        MGR MGR_NAME
----- ---------- ----- ----------
 7839 KING             
 7566 JONES       7839 KING
 7788 SCOTT       7566 JONES
 7876 ADAMS       7788 SCOTT
 7902 FORD        7566 JONES
 7369 SMITH       7902 FORD
 7782 CLARK       7839 KING
 7934 MILLER      7782 CLARK
 
8 rows selected


SQL> with a as(
  2  select empno,ename,mgr,prior ename as mgr_name
  3    from scott.emp
  4   where empno != 7698
  5   start with mgr is null
  6  connect by prior empno = mgr
  7  ),b as(
  8  select empno, ename, mgr, prior ename as mgr_name
  9    from (select * from scott.emp where empno != 7698) emp
 10   start with mgr is null
 11  connect by prior empno = mgr
 12  )
 13  select a.*,b.* from a full outer join b on a.empno = b.empno
 14  where a.empno is null or b.empno is null;
 
EMPNO ENAME        MGR MGR_NAME   EMPNO ENAME        MGR MGR_NAME
----- ---------- ----- ---------- ----- ---------- ----- ----------
 7499 ALLEN       7698 BLAKE                             
 7521 WARD        7698 BLAKE                             
 7654 MARTIN      7698 BLAKE                             
 7844 TURNER      7698 BLAKE                             
 7900 JAMES       7698 BLAKE                             


SQL> select empno, ename, mgr, prior ename as mgr_name
  2    from scott.emp
  3   start with mgr is null
  4  connect by prior empno = mgr
  5         and empno != 7698;
 
EMPNO ENAME        MGR MGR_NAME
----- ---------- ----- ----------
 7839 KING             
 7566 JONES       7839 KING
 7788 SCOTT       7566 JONES
 7876 ADAMS       7788 SCOTT
 7902 FORD        7566 JONES
 7369 SMITH       7902 FORD
 7782 CLARK       7839 KING
 7934 MILLER      7782 CLARK
 
8 rows selected


SQL> with a as(
  2  select empno,ename,mgr,prior ename as mgr_name
  3    from scott.emp
  4   start with mgr is null
  5  connect by prior empno = mgr and empno != 7698
  6  ),b as(
  7  select empno, ename, mgr, prior ename as mgr_name
  8    from (select * from scott.emp where empno != 7698) emp
  9   start with mgr is null
 10  connect by prior empno = mgr
 11  )
 12  select a.*,b.* from a full outer join b on a.empno = b.empno
 13  where a.empno is null or b.empno is null;
 
EMPNO ENAME        MGR MGR_NAME   EMPNO ENAME        MGR MGR_NAME
----- ---------- ----- ---------- ----- ---------- ----- ----------
 
SQL>