WITH t AS ( SELECT 3000 no, 2000 pno, 'GS 테스트1' nm FROM dual UNION ALL SELECT 2000, 1000, 'GS 테스트2' FROM dual UNION ALL SELECT 1000, NULL, 'GS 테스트3' FROM dual UNION ALL SELECT 3100, 2100, 'CU 테스트0' FROM dual UNION ALL SELECT 2100, 1100, 'CU 테스트3' FROM dual UNION ALL SELECT 1100, NULL, 'CU 테스트3' FROM dual ) SELECT DISTINCT * FROM t WHERE CONNECT_BY_ISLEAF = 1 START WITH INSTR(nm, '테스트3') > 0 CONNECT BY PRIOR no = pno ;