Use of mysql recursive query syntax WITH RECURSIVE

WITH RECURSIVEIt is a syntax used in SQL for performing recursive queries, which is particularly suitable for processing hierarchical structures or recursive data (such as tree structures, graph structures). Recursive queries can repeatedly reference themselves to query multi-level data without writing multiple nested queries.

Basic syntax structure:

WITH RECURSIVE CTE_name AS (
    -- Basic query part (Non-recursive part)
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition

    UNION ALL

    -- Recursive query part
    SELECT column1, column2, ...
    FROM table_name t
    JOIN CTE_name cte ON  = 
    WHERE condition
)
SELECT * FROM CTE_name;

Key parts analysis:

• WITH RECURSIVE：

  • WITHUsed to create a common table expression (CTE),RECURSIVEKeyword identification This is a recursive query.
  • CTE_nameIt is the name you give to the public table expression (CTE), which can be referenced after subsequent queries.

• Basic query (non-recursive part):

  • This is the starting point for recursive queries to query root data (usually the uppermost or initial data) in the hierarchy.
  • Usually, this part of the query returns a starting set or base condition, such as the root node in a tree structure.

• Recursive query part:

  • The recursive query part usually refers to (references) the results of the above basic query to form a process of continuous iteration.
  • In the recursive query section, it is often usedJOINOr connect toCTE_name(i.e. the result set of recursive query) connect and find the subordinate data.
  • Recursive queries gradually go deeper until there is no more data.

• UNION ALL：

  • UNION ALLUsed to merge the basic query (non-recursive part) and the recursive query part into a complete result set.
  • UNION ALLWill not be deduplicated (different fromUNION), usually used for recursive queries to keep all results.

• Final query:

  • QueryCTE_name, get the final result of the recursive query.
  • The result of a recursive query returns data at all levels until there are no more levels.

Workflow of recursive query:

• The first iteration:

  • Execute the basic query section, returning the initial dataset (usually the top-level data).

• Second and subsequent iterations:

  • The recursive query part will continue based on the results of the previous query and look for the next level of data (such as looking for all root nodes child nodes).
  • Each iteration adds new rows to the result set.

• Stop conditions:

  • When the recursive query cannot find more rows that meet the criteria, the recursive query stops and returns the final result.

Example: Employee and Manager's Hierarchy

Suppose there is an employee table, and each employee has onemanager_idThe field points to their manager, and we want to query a certain employee and all of their superiors until the top manager.

WITH RECURSIVE EmployeeHierarchy AS (
    -- Basic query part：Find a specific employee
    SELECT id, name, manager_id
    FROM employees
    WHERE id = :employee_id  -- Find designated employees

    UNION ALL

    -- Recursive query part：Finding a manager for employees
    SELECT , , e.manager_id
    FROM employees e
    JOIN EmployeeHierarchy eh ON  = eh.manager_id
)
-- Return to all employees and their superiors
SELECT * FROM EmployeeHierarchy;

explain:

• Basic query part:

  • Find specific employees (byid = :employee_id）。

• Recursive query part:

  • By self-connectionJOIN EmployeeHierarchy eh ON = eh.manager_idFind the manager of the employee (manager_idThe employee pointed to by the field).

• UNION ALL：

  • Merge the basic query part (initial employee) and the recursive query part (find manager level upward).

• Query the final result:

  • Returns the result of the recursive query, i.e. the employee and all his superiors.

Example: Data in a tree structure (such as classification)

Suppose there is a table containing classificationscategories, each category has oneparent_idThe field points to its parent category. We want to query a certain category and all its subcategories.

WITH RECURSIVE CategoryHierarchy AS (
    -- Basic query part：Find a specific category
    SELECT id, name, parent_id
    FROM categories
    WHERE id = :category_id  -- Find a specified category

    UNION ALL

    -- Recursive query part：Find subcategories of categories
    SELECT , , c.parent_id
    FROM categories c
    JOIN CategoryHierarchy ch ON c.parent_id = 
)
-- Return all categories and their subcategories
SELECT * FROM CategoryHierarchy;

explain:

• Basic query part:

  • Find the specified category (byid = :category_id）。

• Recursive query part:

  • Find all subcategories,JOINOperation passedc.parent_id = To connect the parent classification and the child classification.

• UNION ALL：

  • Merge the basic query and recursive query parts and find all subcategories layer by layer.

Features of recursive query:

• Recursive depth limit:

  • Most database systems (such as PostgreSQL, MySQL, etc.) limit the depth of recursive queries to prevent infinite recursion. MySQL defaults to 1000 layer depth, but this value can be adjusted by configuration.

• Performance issues:

  • Recursive queries may consume more resources, especially when there are many levels or large data volumes. Careful use is required to avoid performance bottlenecks.

• Iteration process:

  • Recursive queries are gradually queried downwards through each round of iterations until there is no more data. The results of each iteration will be referenced in the next query.

Summarize:

• WITH RECURSIVEApplicable to processing hierarchical structures or recursive relationships, allowing you to repeatedly refer to yourself in queries and find multi-level data.
• It consists of a basic query (non-recursive part) and a recursive query part, throughUNION ALLConnect the two parts and gradually expand the results.
• When using recursive queries, you need to pay attention to recursive depth limitations and performance impacts.

This is the end of this article about the use of mysql recursive query syntax WITH RECURSIVE. For more related contents of mysql WITH RECURSIVE, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!