Oracle HIERARCHICAL QUERY LEVEL
Oracle HIERARCHICAL QUERY LEVEL is a SQL statement in the Oracle Specific category. Oracle hierarchical queries use CONNECT BY PRIOR with LEVEL, CONNECT_BY_ROOT, and SYS_CONNECT_BY_PATH. The syntax is SELECT ... CONNECT BY PRIOR ... START WITH ... ORDER SIBLINGS BY ...;. It returns hierarchical query result. A typical example: SELECT employee_id, name, manager_id, LEVEL, CONNECT_BY_ROOT name AS root_manager, SYS_CONNECT_BY_PATH(name, '/') AS org_path, CONNECT_BY_ISLEAF AS is_leaf FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR employee_id = manager_id ORDER SIBLINGS BY name; -- LEVEL: 1=CEO, 2=direct reports, 3=etc. -- CONNECT_BY_ROOT: top-most ancestor -- SYS_CONNECT_BY_PATH: full path from root -- CONNECT_BY_ISLEAF: 1 if leaf node (no children) -- ORDER SIBLINGS BY: orders children within same parent A close relative is SEQUENCE (Oracle), which oracle: generates sequential numbers. Independent of tables. Accessed via NEXTVAL and CURRVAL. A close relative is ROWNUM / FETCH FIRST, which oracle: ROWNUM assigns sequential numbers to rows. Used for limiting results (pre-12c).