Hierarchical queries(connect prior) in Oracle 19c SQL are used to retrieve data that has a parent-child relationship, often visualized as a tree structure. This is incredibly useful for organizational charts, bill of materials, network topologies, and threaded discussions. The CONNECT BY PRIOR clause is at the heart of these queries, allowing you to traverse the hierarchy either from parent to child or child to parent. This feature is a powerful tool for navigating complex relationships within your relational data.

Example 1: Basic Hierarchical Query

SELECT
    employee_id,
    first_name,
    last_name,
    manager_id
FROM
    employees
START WITH
    employee_id = 100 -- Specifies the root of the hierarchy (e.g., CEO)
CONNECT BY PRIOR
    employee_id = manager_id; -- Defines the parent-child relationship

Explanation This query retrieves the employee hierarchy starting with the employee whose employee_id is 100. The CONNECT BY PRIOR employee_id = manager_id clause tells Oracle to link rows where the employee_id of the prior (parent) row matches the manager_id of the current (child) row, effectively traversing down the organizational tree. This is a fundamental concept for understanding Oracle's hierarchical query capabilities.

Example 2: Modeling Tree Structures with LEVEL

SELECT
    LEVEL, -- Pseudocolumn indicating the depth in the hierarchy
    employee_id,
    first_name,
    last_name,
    manager_id
FROM
    employees
START WITH
    employee_id = 100
CONNECT BY PRIOR
    employee_id = manager_id
ORDER SIBLINGS BY
    last_name; -- Orders siblings at the same level

Explanation In this example, we introduce the LEVEL pseudocolumn. LEVEL returns a number indicating the depth of the current row in the hierarchical tree, with the START WITH row being LEVEL 1. This is invaluable for understanding the structure of your hierarchical data. The ORDER SIBLINGS BY clause ensures that rows at the same hierarchical level are ordered according to the specified column, providing a more organized output for your Oracle 19c hierarchical query results.

Example 3: Indenting Hierarchical Output with LPAD

SELECT
    LEVEL,
    LPAD(' ', 2 * (LEVEL - 1)) || first_name AS indented_name, -- Indents based on level
    employee_id,
    manager_id
FROM
    employees
START WITH
    employee_id = 100
CONNECT BY PRIOR
    employee_id = manager_id
ORDER SIBLINGS BY
    first_name;

Explanation This query utilizes the LPAD function to visually indent the first_name based on its LEVEL in the hierarchy. LPAD(' ', 2 * (LEVEL - 1)) adds spaces before the name, creating a clear tree-like representation. This technique is widely used in Oracle SQL tutorials and real-world applications to make hierarchical query output more readable and intuitive, greatly enhancing the user experience when navigating complex data structures. This is a common and effective way to present hierarchical data in a user-friendly format, a key for any Oracle 19c SQL developer.