CTE แบบเรียกซ้ำและคำสั่งค้นหากราฟ

Many real-world datasets have a natural hierarchy. Think of an organizational chart where employees report to managers, or a bill of materials where components are made of sub-components.

Standard SQL queries can struggle to navigate these relationships efficiently across multiple levels without complex, nested subqueries or joins. This is where Recursive Common Table Expressions shine!

A Common Table Expression (CTE) acts like a temporary, named result set you can reference within a single SQL statement. They improve readability and organize complex queries.

A Recursive CTE is special because it can refer to itself, allowing it to repeatedly execute to process hierarchical or graph-like data. It's perfect for "find all descendants" or "trace a path" types of problems.

Every recursive CTE has two main parts, combined with UNION ALL. The first is the base member.

This non-recursive part defines the initial set of rows for the recursion. It's the "root" or starting point of your traversal. Think of it as the first step in your journey through the data.

Let's use an employees table with employee_id, name, and manager_id.

The second part is the recursive member. This part references the CTE itself (subordinates in our example) and joins it with the base table (employees) to find the next level of data.

It runs repeatedly, processing the results from the previous iteration, until no new rows are returned. This is the "step-by-step" part of the journey.

A recursive CTE needs a way to stop! The recursion automatically terminates when the recursive member produces no new rows. If it kept finding new rows forever, you'd have an infinite loop!

It's crucial that your recursive member's join condition and filters eventually stop matching rows, ensuring the query finishes. In our example, it stops when there are no more employees whose manager_id matches an employee_id found so far.

Let's put it all together to find all subordinates of 'Alice' (employee ID 1), along with their reporting level.

The base member starts with Alice. The recursive member then finds Alice's direct reports (level 1), then their reports (level 2), and so on, until no more subordinates are found.

You might wonder why we use UNION ALL and not just UNION.

• UNION ALL: Combines all rows from both result sets, including duplicates. It's generally faster because it doesn't need to check for and remove duplicates.
• UNION: Combines rows and removes any duplicates. In a recursive CTE, duplicate checking can add significant overhead and is often not necessary if your logic ensures unique paths or elements at each level.

For recursive traversals, UNION ALL is almost always preferred unless you specifically need to eliminate duplicates that your logic might produce.

Recursive CTEs are also powerful for graph traversal. Imagine finding all connections in a social network or tracing dependencies.

Let's use a simple connections table to find all people connected to 'Alice' (ID 1) up to 2 levels deep.

Recursive CTEs can be powerful, but also resource-intensive if not managed well. Here are some tips:

• Limit Depth: Always include a termination condition for depth (like level < max_depth) to prevent infinite loops or excessively long queries.
• Index Keys: Ensure columns used in join conditions (e.g., employee_id, manager_id, person_id, connected_to_id) are indexed.
• Filter Early: Apply filters in the base member to reduce the initial dataset.
• Avoid Cycles: If your data can contain cycles (e.g., A -> B -> A), you might need to track the path taken (e.g., an array of visited nodes) to prevent infinite loops. PostgreSQL 14+ offers CYCLE clause for this.

You've explored the power of Recursive CTEs!

• They are essential for querying hierarchical data (like organizational charts) and performing graph traversals (like finding paths or connections).
• A recursive CTE consists of a base member (starting point) and a recursive member (iterative step), combined by UNION ALL.
• Recursion stops when the recursive member produces no new rows, but adding a depth limit is often a good practice.
• Always consider indexing relevant columns and filtering early for optimal performance.

Mastering recursive CTEs opens up new possibilities for querying complex, interconnected datasets in PostgreSQL!