Hierarchical queries in SQL allow you to retrieve data that is structured in a tree-like format. In Oracle databases, you can use the CONNECT BY clause to perform hierarchical queries. Here's an overview of how to use it, along with examples:
Basic Syntax
The basic syntax of a hierarchical query using CONNECT BY looks like this:
SELECT column_list
FROM table_name
START WITH condition
CONNECT BY PRIOR parent_column = child_column;
Key Components
- SELECT: This specifies which columns you want to retrieve.
- FROM: This indicates the table from which you are selecting data.
- START WITH: This clause identifies the root of the hierarchy (the starting point of the traversal).
- CONNECT BY PRIOR: This clause specifies the relationship between parent and child rows. The PRIOR keyword is used to refer to the parent row in the hierarchical relationship.
Example
Assume you have a table named employees that contains the columns employee_id, employee_name, and manager_id:
CREATE TABLE employees (
employee_id NUMBER,
employee_name VARCHAR2(100),
manager_id NUMBER
);
Here's how to populate the table with some sample data:
INSERT INTO employees (employee_id, employee_name, manager_id) VALUES (1, 'CEO', NULL);
INSERT INTO employees (employee_id, employee_name, manager_id) VALUES (2, 'Manager 1', 1);
INSERT INTO employees (employee_id, employee_name, manager_id) VALUES (3, 'Manager 2', 1);
INSERT INTO employees (employee_id, employee_name, manager_id) VALUES (4, 'Employee 1', 2);
INSERT INTO employees (employee_id, employee_name, manager_id) VALUES (5, 'Employee 2', 2);
INSERT INTO employees (employee_id, employee_name, manager_id) VALUES (6, 'Employee 3', 3);
Querying the Hierarchy
To retrieve the hierarchical structure of the employees, you would write the following query:
SELECT employee_id, employee_name, manager_id
FROM employees
START WITH manager_id IS NULL -- This indicates that we start with the CEO
CONNECT BY PRIOR employee_id = manager_id; -- Establishes parent-child relationship
Explanation
- START WITH manager_id IS NULL: This identifies the root of the hierarchy (in this case, the CEO).
- CONNECT BY PRIOR employee_id = manager_id: This specifies that you want to connect rows where the manager_id of the child matches the employee_id of the parent.
Output
The result of the query would return something like this:
EMPLOYEE_ID EMPLOYEE_NAME MANAGER_ID
1 CEO NULL
2 Manager 1 1
4 Employee 1 2
5 Employee 2 2
3 Manager 2 1
6 Employee 3 3
Additional Options
You can also use hierarchical-specific pseudo-columns like:
- LEVEL: This pseudo-column indicates the level of the node in the tree (the root node has a level of 1, its children have a level of 2, etc.).
- SYS_CONNECT_BY_PATH: This function returns the path from the root to the current row, representing the hierarchy.
Example using LEVEL:
SELECT employee_id,
employee_name,
LEVEL AS hierarchy_level
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;
This provides a deeper insight into the hierarchy's structure.
Conclusion
Using the CONNECT BY clause in Oracle allows you to easily perform hierarchical queries, making it a powerful feature for retrieving and understanding tree-structured data from relational databases.