树形查询中where的作用

start with定义如下

SELECT  Is the standard SELECT clauseLEVEL 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 selectedSQL> 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 selectedSQL> 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 KINGSCOTT       7566 JONESADAMS       7788 SCOTTFORD        7566 JONESSMITH       7902 FORDALLEN       7698 BLAKEWARD        7698 BLAKEMARTIN      7698 BLAKETURNER      7698 BLAKEJAMES       7698 BLAKECLARK       7839 KINGMILLER      7782 CLARK 13 rows selectedSQL> 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 selectedSQL> 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 selectedSQL> 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>