connect_by_isleaf_cte.sql

-- connect_by_isleaf with standard connect by

select lpad(' ',2*(level-1)) || last_name last_name, connect_by_isleaf
from hr.employees
start with manager_id is null
connect by prior employee_id = manager_id;

/*

LAST_NAME            CONNECT_BY_ISLEAF
-------------------- -----------------
King                                 0
  Kochhar                            0
    Greenberg                        0
      Faviet                         1
      Chen                           1
      Sciarra                        1
      Urman                          1
      Popp                           1
    Whalen                           1
    Mavris                           1
    Baer                             1
    Higgins                          0
      Gietz                          1
  De Haan                            0
    Hunold                           0
      Ernst                          1
...

*/

-- Now using recursive subquery factoring

with leaves as (
	select employee_id
	from hr.employees
	where employee_id not in (
		select manager_id
		from hr.employees
		where manager_id is not null
	)
),
emp(manager_id,employee_id,last_name,lvl,isleaf) as (
	select e.manager_id, e.employee_id, e.last_name, 1 as lvl, 0 as isleaf
	from hr.employees e
	where e.manager_id is null
	union all
	select e.manager_id, nvl(e.employee_id,null) employee_id,  e.last_name, emp.lvl + 1 as lvl
		, decode(l.employee_id,null,0,1) isleaf
	from hr.employees e
	join emp on emp.employee_id = e.manager_id
	left outer join leaves l on l.employee_id = e.employee_id
	--join hr.employees e on e.manager_id = emp.employee_id
	--join emp on emp.employee_id = e.manager_id
)
search depth first by last_name set order1
select lpad(' ',2*(lvl-1)) || last_name last_name, isleaf
from emp
/

/*

LAST_NAME                ISLEAF
-------------------- ----------
King                          0
  Cambrault                   0
    Bates                     1
    Bloom                     1
    Fox                       1
    Kumar                     1
    Ozer                      1
    Smith                     1
  De Haan                     0
    Hunold                    0
      Austin                  1
...
*/