Hierarchical Queries OR Connect by Queries

For better understanding please go to the YouTube channel.

https://www.youtube.com/oracleshooter

Hierarchical Queries Or Connect by Query in Oracle

Connect by Queries :-

In its simplest form a hierarchical query needs a definition of how each child relates to its parent. This is defined using the CONNECT BY .. PRIOR clause, which defines how the current row (child) relates to a prior row (parent). In addition, the START WITH clause can be used to define the root node(s) of the hierarchy. Hierarchical queries come with operators, pseudocolumns and functions to help make sense of the hierarchy.

• LEVEL : The position in the hierarchy of the current row in relation to the root node.
• CONNECT_BY_ROOT : Returns the root node(s) associated with the current row.
• SYS_CONNECT_BY_PATH : Returns a delimited breadcrumb from root to the current row.
• CONNECT_BY_ISLEAF : Indicates if the current row is a leaf node.
• ORDER SIBLINGS BY : Applies an order to siblings, without altering the basic hierarchical structure of the data returned by the query.

• 
  

Pseudo Columns :-

1. LEVEL

2. CONNECT_BY_ISLEAF

3. CONNECT_BY_ISCYCLE

Ex:-

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-Dec-80	800		20	Level-3
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30
7521	WARD	SALESMAN	7698	22-Feb-81	1250	500	30
7566	JONES	MANAGER	7839	02-Apr-81	2975		20	Level-1
7654	MARTIN	SALESMAN	7698	28-Sep-81	1250	1400	30
7698	BLAKE	MANAGER	7839	01-May-81	2850		30
7782	CLARK	MANAGER	7839	09-Jun-81	2450		10
7788	SCOTT	ANALYST	7566	09-Dec-82	3000		20	Level-2
7839	KING	PRESIDENT		17-Nov-81	5000		10
7844	TURNER	SALESMAN	7698	08-Sep-81	1500	0	30
7876	ADAMS	CLERK	7788	12-Jan-83	1100		20	Level-3
7900	JAMES	CLERK	7698	03-Dec-81	950		30
7902	FORD	ANALYST	7566	03-Dec-81	3000		20	Level-2
7934	MILLER	CLERK	7782	23-Jan-82	1300		10

--====================================================

CONNECT BY PRIOR

--====================================================

select * from SCOTT.EMP

--SYNTAX --

SELECT EMPNO, ENAME FROM SCOTT.EMP

START WITH ENAME = 'JONES'

CONNECT BY PRIOR EMPNO = MGR

--ORDER BY LEVEL

EMPNO	ENAME
7566	JONES
7788	SCOTT
7876	ADAMS
7902	FORD
7369	SMITH

--====================================================

--How to use PRIOR in SELECT

--====================================================

SELECT EMPNO, ENAME, PRIOR ENAME MANAGER FROM SCOTT.EMP

START WITH ENAME = 'JONES'

CONNECT BY PRIOR EMPNO = MGR

ORDER BY LEVEL

EMPNO	ENAME	MANAGER
7566	JONES
7902	FORD	JONES
7788	SCOTT	JONES
7369	SMITH	FORD
7876	ADAMS	SCOTT

--====================================================

CONNECT BY LEVEL

--====================================================

--TOP -to- DOWN  ( it works on both direction )

SELECT EMPNO, ENAME, LEVEL

FROM SCOTT.EMP

START WITH ENAME = 'JONES'

--start with MGR is null

CONNECT BY PRIOR EMPNO = MGR

ORDER BY LEVEL

EMPNO	ENAME	LEVEL
7566	JONES	1
7902	FORD	2
7788	SCOTT	2
7369	SMITH	3
7876	ADAMS	3

--Bottom -to- UP  ( it works on both direction )

SELECT EMPNO, ENAME

, LEVEL

FROM SCOTT.EMP

START WITH ENAME = 'JONES'

--start with MGR is null

CONNECT BY  EMPNO =  PRIOR MGR

ORDER BY LEVEL

EMPNO	ENAME	LEVEL
7566	JONES	1
7839	KING	2

--====================================================

CONNECT BY CONNECT_BY_ROOT

--====================================================

SELECT EMPNO, ENAME

, LEVEL

, CONNECT_BY_ROOT ENAME

FROM SCOTT.EMP

START WITH ENAME = 'KING'

--start with MGR is null

CONNECT BY PRIOR EMPNO = MGR

ORDER BY LEVEL

EMPNO	ENAME	LEVEL	CONNECT_BY_ROOTENAME
7839	KING	1	KING
7566	JONES	2	KING
7698	BLAKE	2	KING
7782	CLARK	2	KING
7902	FORD	3	KING
7521	WARD	3	KING
7900	JAMES	3	KING
7934	MILLER	3	KING
7499	ALLEN	3	KING
7788	SCOTT	3	KING
7654	MARTIN	3	KING
7844	TURNER	3	KING
7876	ADAMS	4	KING
7369	SMITH	4	KING

--====================================================

CONNECT BY SYS_CONNECT_BY_PATH

--====================================================

SELECT EMPNO, ENAME

, LEVEL

, CONNECT_BY_ROOT ENAME

, SYS_CONNECT_BY_PATH(ENAME, ' -> ') PATH

FROM SCOTT.EMP

START WITH ENAME = 'JONES'

--start with MGR is null

CONNECT BY PRIOR EMPNO = MGR

ORDER BY LEVEL

EMPNO	ENAME	LEVEL	CONNECT_BY_ROOTENAME	PATH
7566	JONES	1	JONES	-> JONES
7902	FORD	2	JONES	-> JONES -> FORD
7788	SCOTT	2	JONES	-> JONES -> SCOTT
7369	SMITH	3	JONES	-> JONES -> FORD -> SMITH
7876	ADAMS	3	JONES	-> JONES -> SCOTT -> ADAMS

--====================================================

CONNECT BY CONNECT_BY_ISLEAF

--====================================================

SELECT EMPNO, ENAME

, LEVEL

, CONNECT_BY_ROOT ENAME

, SYS_CONNECT_BY_PATH(ENAME, ' -> ') PATH

, CONNECT_BY_ISLEAF

FROM SCOTT.EMP

START WITH ENAME = 'JONES'

--start with MGR is null

CONNECT BY  PRIOR EMPNO = MGR

ORDER BY LEVEL

EMPNO	ENAME	LEVEL	CONNECT_BY_ROOTENAME	PATH	CONNECT_BY_ISLEAF
7566	JONES	1	JONES	-> JONES	0
7902	FORD	2	JONES	-> JONES -> FORD	0
7788	SCOTT	2	JONES	-> JONES -> SCOTT	0
7369	SMITH	3	JONES	-> JONES -> FORD -> SMITH	1
7876	ADAMS	3	JONES	-> JONES -> SCOTT -> ADAMS	1

--====================================================

CONNECT_BY_ISCYCLE

--====================================================

update SCOTT.EMP

SET MGR = 7902

WHERE EMPNO = 7566

SELECT EMPNO, ENAME

, LEVEL

, CONNECT_BY_ROOT ENAME

, SYS_CONNECT_BY_PATH(ENAME, ' -> ') PATH

, CONNECT_BY_ISLEAF

FROM SCOTT.EMP

START WITH ENAME = 'JONES'

--start with MGR is null

CONNECT BY  PRIOR EMPNO = MGR

ORDER BY LEVEL

SELECT EMPNO, ENAME

, LEVEL

, CONNECT_BY_ROOT ENAME

, SYS_CONNECT_BY_PATH(ENAME, ' -> ') PATH

, CONNECT_BY_ISLEAF

, CONNECT_BY_ISCYCLE

FROM SCOTT.EMP

START WITH ENAME = 'JONES'

--start with MGR is null

CONNECT BY NOCYCLE  EMPNO = PRIOR MGR

ORDER BY LEVEL

EMPNO	ENAME	LEVEL	CONNECT_BY_ROOTENAME	PATH	CONNECT_BY_ISLEAF	CONNECT_BY_ISCYCLE
7566	JONES	1	JONES	-> JONES	0	0
7902	FORD	2	JONES	-> JONES -> FORD	1	1