CONNECT BY (Hierarchical)

CONNECT BY (Hierarchical) is a SQL statement in the Oracle Specific category. Oracle-specific hierarchical query syntax. Builds tree structures (org charts, bill of materials). The syntax is SELECT ... FROM table START WITH condition CONNECT BY PRIOR col = parent_col;. It returns hierarchical result. A typical example: SELECT employee_id, name, manager_id, LEVEL FROM employees START WITH manager_id IS NULL -- Root node (CEO) CONNECT BY PRIOR employee_id = manager_id; -- Traverses org chart top-down -- LEVEL: 1 for root, 2 for children, 3 for grandchildren... -- SYS_CONNECT_BY_PATH function shows full path 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). A close relative is SYSDATE / CURRENT_DATE, which oracle: returns the current date and time of the database server.