What Are Hierarchical Queries and Why Are They Needed
Hierarchical queries are a special type of SQL query designed to work with data that has a tree-like "parent-child" structure. Imagine an organizational chart where each employee has a manager, or a file system where folders contain subfolders.
The Main Problem Hierarchical Queries Solve
In regular relational tables, data is stored "flat"—each row contains a reference to its "parent." For example:
emp_id | name | manager_id
-------|-----------|-----------
1 | Smith | NULL (top manager)
2 | Müller | 1 (subordinate to Smith)
3 | Dubois | 1 (subordinate to Smith)
4 | Rossi | 2 (subordinate to Müller)
To get the full hierarchy (who reports to whom at all levels), it’s extremely difficult to do this with regular JOINs, especially if the depth of nesting is unknown in advance.
Main Areas of Application
1. Organizational Structures
- Employee hierarchies in a company
- Management chains
- Departments and divisions
2. Categories and Classifications
- Product catalogs (category → subcategory → product)
- Tag and rubric systems
- Geographic structures (country → region → city)
3. Technical Structures
- File systems (folders and subfolders)
- Website and application menus
- Comments with replies and nesting
4. Financial and Accounting Systems
- Chart of accounts in accounting
- Budget structures
- Cost center hierarchies
5. Production Processes
- Bill of materials (part → assembly → product)
- Technological routes
- Project task dependencies
Why This Is Important
Without hierarchical queries, to get "all subordinates of Ivanov at all levels," you would have to:
1. Write multiple JOINs (but how many levels?)
2. Use recursive queries or procedures
3. Process data in application code
Example: Product Catalog
Let’s create a table for a product catalog with a hierarchical structure:
-- Creating the product categories table
CREATE TABLE product_categories_en (
category_id INT PRIMARY KEY,
category_name VARCHAR(100),
parent_category_id INT,
category_level VARCHAR(20), -- for clarity
FOREIGN KEY (parent_category_id) REFERENCES product_categories_en(category_id)
);
Explanation of FOREIGN KEY (parent_category_id) REFERENCES product_categories(category_id)
This line creates a foreign key—a constraint that ensures data integrity in the hierarchical structure.
What This Means:
FOREIGN KEY (parent_category_id) — declares that the parent_category_id column is a foreign key
REFERENCES product_categories_en(category_id) — specifies that this foreign key refers to the category_id column in the same product_categories table
How It Works:
- Self-reference: The table refers to itself—this is called a recursive relationship
- Parent-child relationships: Each category can have a parent category
- Root elements: Top-level records have
parent_category_id = NULL
What This Constraint Guarantees:
-- ✅ Allowed: parent_category_id refers to an existing record
INSERT INTO product_categories_en VALUES (100, 'Laptops', 10, 'Level 2');
-- where 10 is an existing category_id ('Computers')
-- ✅ Allowed: parent_category_id = NULL (root element)
INSERT INTO product_categories_en VALUES (1, 'Electronics', NULL, 'Root');
-- ❌ Not allowed: parent_category_id refers to a non-existent record
INSERT INTO product_categories_en VALUES (200, 'Shirts', 999, 'Level 2');
-- Error: no record with category_id = 999
Advantages of Using It:
- Data integrity: You can’t create "orphan" records
- Cascading operations: You can set up automatic deletion of child elements
- Error prevention: The DBMS automatically checks the correctness of relationships
Example of Violation Without FOREIGN KEY:
Without this constraint, you could insert:
-- Without FK this would work, but create a "broken" hierarchy
INSERT INTO product_categories_en VALUES (300, 'New Category', 99999, 'Level 1');
Result: A category referencing a non-existent parent—the hierarchy is broken!
SQL Code to Populate the Test Table:
-- Data population
INSERT INTO product_categories_en VALUES (1, 'Electronics', NULL, 'Root');
INSERT INTO product_categories_en VALUES (2, 'Clothing', NULL, 'Root');
INSERT INTO product_categories_en VALUES (3, 'Home and Garden', NULL, 'Root');
-- Subcategories of Electronics
INSERT INTO product_categories_en VALUES (10, 'Computers', 1, 'Level 1');
INSERT INTO product_categories_en VALUES (11, 'Phones', 1, 'Level 1');
INSERT INTO product_categories_en VALUES (12, 'Home Appliances', 1, 'Level 1');
-- Subcategories of Clothing
INSERT INTO product_categories_en VALUES (20, 'Men''s Clothing', 2, 'Level 1');
INSERT INTO product_categories_en VALUES (21, 'Women''s Clothing', 2, 'Level 1');
INSERT INTO product_categories_en VALUES (22, 'Children''s Clothing', 2, 'Level 1');
-- Subcategories of Computers
INSERT INTO product_categories_en VALUES (100, 'Laptops', 10, 'Level 2');
INSERT INTO product_categories_en VALUES (101, 'Desktops', 10, 'Level 2');
INSERT INTO product_categories_en VALUES (102, 'Tablets', 10, 'Level 2');
-- Subcategories of Phones
INSERT INTO product_categories_en VALUES (110, 'Smartphones', 11, 'Level 2');
INSERT INTO product_categories_en VALUES (111, 'Feature Phones', 11, 'Level 2');
-- Subcategories of Men\'s Clothing
INSERT INTO product_categories_en VALUES (200, 'Shirts', 20, 'Level 2');
INSERT INTO product_categories_en VALUES (201, 'Trousers', 20, 'Level 2');
INSERT INTO product_categories_en VALUES (202, 'Suits', 20, 'Level 2');
-- Subcategories of Smartphones
INSERT INTO product_categories_en VALUES (1100, 'iPhone', 110, 'Level 3');
INSERT INTO product_categories_en VALUES (1101, 'Samsung', 110, 'Level 3');
INSERT INTO product_categories_en VALUES (1102, 'Xiaomi', 110, 'Level 3');
-- Subcategories of Shirts
INSERT INTO product_categories_en VALUES (2000, 'Business Shirts', 200, 'Level 3');
INSERT INTO product_categories_en VALUES (2001, 'Casual Shirts', 200, 'Level 3');
Visual Structure of the Catalog:
├── Electronics (1)
│ ├── Computers (10)
│ │ ├── Laptops (100)
│ │ ├── Desktops (101)
│ │ └── Tablets (102)
│ ├── Phones (11)
│ │ ├── Smartphones (110)
│ │ │ ├── iPhone (1100)
│ │ │ ├── Samsung (1101)
│ │ │ └── Xiaomi (1102)
│ │ └── Feature Phones (111)
│ └── Home Appliances (12)
├── Clothing (2)
│ ├── Men\'s Clothing (20)
│ │ ├── Shirts (200)
│ │ │ ├── Business Shirts (2000)
│ │ │ └── Casual Shirts (2001)
│ │ ├── Trousers (201)
│ │ └── Suits (202)
│ ├── Women\'s Clothing (21)
│ └── Children\'s Clothing (22)
└── Home & Garden (3)
You can get the structure with this SELECT:
SELECT
LEVEL,
LPAD(' ', (LEVEL-1)*4) || category_name AS tree_view,
category_id,
parent_category_id
FROM product_categories_en
START WITH parent_category_id IS NULL
CONNECT BY PRIOR category_id = parent_category_id
ORDER SIBLINGS BY category_name;
In my Oracle database, the result of this query looks like this:
