Oracle SQL – Using START WITH and CONNECT BY

oracleoracle-12c

I have a question on the START WITH and CONNECT BY condition (in Oracle 12c):

This is my Oracle SQL:

SELECT 
    employee_id, 
    lpad(' ', level*2-1,' ') || last_name, 
    manager_id, 
    level,
    SYS_CONNECT_BY_PATH(last_name, '/') "Path" --returns the full path of the child
FROM 
    hr.employees
start with employee_id = 100
CONNECT BY PRIOR employee_id = manager_id 
AND NOT (last_name = 'Cambrault' and first_name = 'Gerald')
order siblings by last_name

which is giving the following output:

EMPLOYEE_ID     LAST_NAME           MANAGER_ID  LEVEL   PATH
100             King                    null    1   /King
102                 De Haan             100     2   /King/De Haan
103                     Hunold          102     3   /King/De Haan/Hunold
105                         Austin      103     4   /King/De Haan/Hunold/Austin
104                         Ernst       103     4   /King/De Haan/Hunold/Ernst
107                         Lorentz     103     4   /King/De Haan/Hunold/Lorentz
106                         Pataballa   103     4   /King/De Haan/Hunold/Pataballa

But when I remove the START WITH in the above query:

SELECT 
    employee_id, 
    lpad(' ', level*2-1,' ') || last_name, 
    manager_id, 
    level,
    SYS_CONNECT_BY_PATH(last_name, '/') "Path" --returns the full path of the child
FROM 
    hr.employees
CONNECT BY PRIOR employee_id = manager_id 
AND NOT (last_name = 'Cambrault' and first_name = 'Gerald')
order siblings by last_name

it gives the following result:

EMPLOYEE_ID     LAST_NAME   MANAGER_ID  LEVEL   PATH
148             Cambrault   100         1       /Cambrault
172                 Bates   148         2       /Cambrault/Bates
169                 Bloom   148         2       /Cambrault/Bloom
170                 Fox     148         2       /Cambrault/Fox
173                 Kumar   148         2       /Cambrault/Kumar
168                 Ozer    148         2       /Cambrault/Ozer
171                 Smith   148         2       /Cambrault/Smith

Can you please explain why despite giving using the condition NOT (last_name = 'Cambrault' and first_name = 'Gerald') it has no effect, whereas it works only when used with START WITH (starting the hierarchy from the employee_id = 100). I understand the condition in CONNECT BY has no effect on the root row(s) but employee_id (148) is not root; it is just a parent row so why it is not working?

Best Answer

When you have CONNECT BY without START WITH, the root, starting points of the recursion are all the rows of the table. It is useful if you want to find all the hierarchies under all employees.

The condition in CONNECT BY is checked only for the next levels of recursion, so only for siblings.

You can modify START WITH to get everything except Cambrault:
(note that this way, an employee may appear multiple times, one as individual, one as a child of their manager, another as a child of their manager, another as a grandchild of their manager's manager, etc.
(so this is probably not what you want): 

START WITH 
      NOT (last_name = 'Cambrault' AND first_name = 'Gerald')

or all hierarchies under top level managers (employers without a manager). Everyone will appear once this way:

START WITH manager_id IS NULL

or all hierarchies under top level managers (employers without a manager) except Cambrault's.
Note that this will give you the same results as the previous START WITH with current data (but will differ when Cambrault is promoted to top level manager):

START WITH manager_id IS NULL
  AND NOT (last_name = 'Cambrault' AND first_name = 'Gerald')

Test in dbfiddle.uk.

Related Question