How to identify rows that are not parents of any other rows in a Hierarchy table in Oracle?

Problem Statement: How to identify leaf rows in a Hierarchy table i.e. rows that are not parents of any other rows.

Solution: Oracle provides CONNECT_BY_ISLEAF clause to identify rows that are not parents of any other rows. To begin with let us see how does a connect_by_isleaf works.

SQL:

/*
  Function - Example to show if the row is parent of any other rows or not
  Tables Used - students Data - Documented below
*/
SELECT student_id,
       level,
       CASE WHEN connect_by_isleaf = 0
            THEN 'Yes'
            ELSE 'No'
        END AS is_parent,
       lpad('-',(level-1)*3) || first_name || ' ' || last_name AS full_name
  FROM students
START WITH professor_id IS null
CONNECT BY PRIOR student_id  = professor_id;

Output: Few rows from above SQL

<p>100 1   Yes SMITH JAMES
101 2   Yes   -JOHNSON JOHN
108 3   Yes      -RODRIGUEZ JOSEPH
109 4   No          -WILSON THOMAS
110 4   No          -MARTINEZ CHRISTOPHER
111 4   No          -ANDERSON DANIEL
112 4   No          -TAYLOR PAUL
113 4   No          -THOMAS MARK
102 2   Yes   -WILLIAMS ROBERT
103 3   Yes      -BROWN MICHAEL
104 4   No          -JONES WILLIAM
105 4   No          -MILLER DAVID</p>

In the above SQL the value 0 for connect_by_isleaf represents that the row is parent and have further child rows while a value of 1 represent the leaf node/record which is not a parent of anyother rows.

One of the interesting implementation of connect_by_isleaf is to identify the students that are not professors/adminstrators. Below SQL will show how to acheive it.

SQL:

/*
  Function - Example to identify students that are not professors/adminstrators
  Tables Used - students Data - Documented below
*/
SELECT student_id,first_name || ' ' || last_name AS student_name,
       connect_by_root first_name || ' ' || last_name AS manager_name
  FROM students
WHERE connect_by_isleaf = 1
START WITH professor_id IS null
CONNECT BY PRIOR student_id  = professor_id;

Output: Few rows from above SQL

<p>109 WILSON THOMAS   SMITH THOMAS
110 MARTINEZ CHRISTOPHER    SMITH CHRISTOPHER
111 ANDERSON DANIEL SMITH DANIEL
112 TAYLOR PAUL SMITH PAUL
113 THOMAS MARK SMITH MARK
104 JONES WILLIAM   SMITH WILLIAM
105 MILLER DAVID    SMITH DAVID
106 DAVIS RICHARD   SMITH RICHARD
107 GARCIA CHARLES  SMITH CHARLES</p>

Data Preparation: Data used for the problem is shown below. The data is completely made up for demonstration purpose.

Example

DROP TABLE students;
COMMIT;

CREATE TABLE students
    ( student_id     NUMBER(6)
    , first_name     VARCHAR2(20)
    , last_name      VARCHAR2(25) 
    , email          VARCHAR2(40) 
    , phone_number   VARCHAR2(20)
    , join_date      DATE 
    , class_id       VARCHAR2(20)  
    , fees           NUMBER(8,2)
    , professor_id   NUMBER(6)
    , department_id  NUMBER(4) 
    ) ;

Example

CREATE UNIQUE INDEX stu_id_pk ON students (student_id) ;
INSERT INTO students VALUES (100,'SMITH','JAMES','SMITH.JAMES@notreal.com','111.111.1245',TO_DATE('17-06-2003','DD-MM-YYYY'),'INS_CHAIRMAN',24000,NULL,NULL);
INSERT INTO students VALUES (101,'JOHNSON','JOHN','JOHNSON.JOHN@notreal.com','111.111.1246',TO_DATE('21-09-2005','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (102,'WILLIAMS','ROBERT','WILLIAMS.ROBERT@notreal.com','111.111.1247',TO_DATE('13-01-2001','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (103,'BROWN','MICHAEL','BROWN.MICHAEL@notreal.com','111.111.1248',TO_DATE('03-01-2006','DD-MM-YYYY'),'INS_STAFF',9000,102,60);
INSERT INTO students VALUES (104,'JONES','WILLIAM','JONES.WILLIAM@notreal.com','111.111.1249',TO_DATE('21-05-2007','DD-MM-YYYY'),'INS_STAFF',6000,103,60);
INSERT INTO students VALUES (105,'MILLER','DAVID','MILLER.DAVID@notreal.com','111.111.1250',TO_DATE('25-06-2005','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (106,'DAVIS','RICHARD','DAVIS.RICHARD@notreal.com','111.111.1251',TO_DATE('05-02-2006','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (107,'GARCIA','CHARLES','GARCIA.CHARLES@notreal.com','111.111.1252',TO_DATE('07-02-2007','DD-MM-YYYY'),'INS_STAFF',4200,103,60);
INSERT INTO students VALUES (108,'RODRIGUEZ','JOSEPH','RODRIGUEZ.JOSEPH@notreal.com','111.111.1253',TO_DATE('17-08-2002','DD-MM-YYYY'),'CL_PHY',12008,101,100);
INSERT INTO students VALUES (109,'WILSON','THOMAS','WILSON.THOMAS@notreal.com','111.111.1254',TO_DATE('16-08-2002','DD-MM-YYYY'),'CL_MATH',9000,108,100);
INSERT INTO students VALUES (110,'MARTINEZ','CHRISTOPHER','MARTINEZ.CHRISTOPHER@notreal.com','111.111.1255',TO_DATE('28-09-2005','DD-MM-YYYY'),'CL_MATH',8200,108,100);
INSERT INTO students VALUES (111,'ANDERSON','DANIEL','ANDERSON.DANIEL@notreal.com','111.111.1256',TO_DATE('30-09-2005','DD-MM-YYYY'),'CL_MATH',7700,108,100);
INSERT INTO students VALUES (112,'TAYLOR','PAUL','TAYLOR.PAUL@notreal.com','111.111.1257',TO_DATE('07-03-2006','DD-MM-YYYY'),'CL_MATH',7800,108,100);
INSERT INTO students VALUES (113,'THOMAS','MARK','THOMAS.MARK@notreal.com','111.111.1258',TO_DATE('07-12-2007','DD-MM-YYYY'),'CL_MATH',6900,108,100);

COMMIT;