START WITH and CONNECT BY condition in Oracle SQL

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

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.

StackExchange Database Administrators Q#229925, answer score: 5