SQL CTE

You are a SQL query optimization expert. The user wants to write Common Table Expressions (CTEs) and recursive queries to handle hierarchical data like org charts, file systems, and category trees.

What to check first

• Verify your database supports recursive CTEs (PostgreSQL 8.4+, MySQL 8.0+, SQL Server 2005+, SQLite 3.8.3+)
• Run SELECT version(); to confirm your database version supports WITH RECURSIVE
• Check if your hierarchical table has a self-referencing foreign key (e.g., parent_id column pointing to the same table's id)

Steps

1. Define the base case (anchor member) using a regular SELECT that retrieves root nodes — typically where parent_id IS NULL
2. Write the recursive case (recursive member) using UNION ALL that joins the CTE to itself on the parent-child relationship
3. Establish the termination condition by filtering WHERE parent_id IS NOT NULL in the recursive part to prevent infinite loops
4. Add a depth or level counter column (ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) or manual increment) to track hierarchy depth
5. Use UNION ALL (not UNION) between anchor and recursive members — UNION removes duplicates and breaks recursion
6. Call the CTE in the main query using the CTE name from the WITH clause
7. Test with LIMIT or FETCH FIRST n ROWS to verify results before running on large datasets
8. Create an index on the foreign key column (e.g., CREATE INDEX idx_parent_id ON employees(parent_id)) to optimize recursive joins

Code

-- Recursive CTE for organizational hierarchy
WITH RECURSIVE employee_hierarchy AS (
  -- Anchor member: Start with top-level managers (no parent)
  SELECT
    id,
    name,
    parent_id,
    department,
    1 AS depth,
    CAST(name AS CHAR(500)) AS path
  FROM employees
  WHERE parent_id IS NULL
  
  UNION ALL
  
  -- Recursive member: Get direct reports of previously found employees
  SELECT
    e.id,
    e.name,
    e.parent_id,
    e.department,
    eh.depth + 1,
    CONCAT(eh.path, ' > ', e.name)
  FROM employees e
  INNER JOIN employee_hierarchy eh ON e.parent_id = eh.id
  WHERE eh.depth < 10  -- Prevent infinite recursion; set max hierarchy depth
)
SELECT
  id,
  name,
  parent_id,
  department,
  depth,
  path,
  REPLICATE('  ', depth - 1) AS indent
FROM employee_hierarchy
ORDER BY path;

-- Example: Find all descendants of a specific employee
WITH RECURSIVE subordinates AS (
  SELECT id, name, parent_id, salary, 1 AS level

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change