Learn the steps to write recursive queries in SQL using common table expressions and UNION ALL. Find out how to limit, sort, and optimize the recursive results.

A simple example of a company structure can be written like this in SQL Server: declare @emp table (EmployeeID INT PRIMARY KEY,EmployeeName NVARCHAR(50), ManagerID INT) INSERT INTO @emp (EmployeeID, EmployeeName, ManagerID) VALUES (1, 'John', NULL),(2, 'Alice', 1),(3, 'Bob', 1),(4, 'Charlie', 2),(5, 'David', 2),(6, 'Eva', 3),(7, 'Frank', 3); WITH EmployeeHierarchyCTE AS ( SELECT EmployeeID,EmployeeName,ManagerID, 0 AS Level FROM @emp WHERE ManagerID IS NULL UNION ALL SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, eh.Level + 1 AS Level FROM @emp e INNER JOIN EmployeeHierarchyCTE eh ON e.ManagerID = eh.EmployeeID ) SELECT * FROM EmployeeHierarchyCTE; Note the anchor to head (managerID is null)

What are the steps to implement recursive queries in SQL?

Recursive queries are a powerful way to retrieve data from hierarchical or self-referential tables in SQL. They allow you to traverse a tree or a graph structure and return the results in a single query, without using loops or cursors. In this article, you will learn the steps to implement recursive queries in SQL using common table expressions (CTEs) and the UNION ALL operator.

1 What is a recursive query?

A recursive query is a type of query that uses a CTE to define a base case and a recursive case. A CTE is a temporary result set that can be referenced within the same query. The base case is the initial set of rows that satisfies a condition. The recursive case is the set of rows that is derived from the previous set by applying a join or a filter. The recursive query repeats the recursive case until no more rows are generated or a limit is reached.

Add your perspective

Bevan Ward

Principal Advisor Innovation - Data and Information Management at Rio Tinto
Report contribution
CTE queries are a got to for making representations of hierarchical data. For example an org structure. The key for formulating is to anchor against the top record - so in this example the CEO, then resolve the reports to the CEO, etc as part of a top down traverse. It is important to set a recursion limit. It is useful to increment a level counter so the resulting query shows the recursion depth.

Like
Drashti Bhatt

Data Scientist | Machine Learning
Report contribution
Create Recursive CTE: Use WITH RECURSIVE to define a recursive common table expression (CTE). Base Query: Write a base query selecting the initial set of data with a specified condition (e.g., where parent_id is NULL). Recursive Query: In the recursive part, join the table with the recursive CTE to fetch related rows. Union Base and Recursive Queries: Combine the base and recursive queries using UNION ALL within the CTE. Select from Recursive CTE: Use a SELECT statement outside the CTE to retrieve the final result set. For optimizing the performance of recursive queries in SQL, tips are: Indexes, Analyze Execution Plan, Limit Result Set, Avoid Excessive Recursion, Use Proper Data Types, Partitioning (if applicable), and Batch Processing.

Like

2 How to write a recursive CTE?

To write a recursive CTE, you need to use the WITH keyword followed by the name of the CTE and the column names. Then, you need to use the UNION ALL operator to combine the base case and the recursive case. The base case and the recursive case must have the same number and type of columns. The recursive case must refer to the CTE name in the FROM clause. For example, the following query returns the ancestors of a given employee in an organization:

WITH ancestor (emp_id, emp_name, mgr_id, level) AS (
  -- base case: select the employee with emp_id = 100
  SELECT emp_id, emp_name, mgr_id, 1 AS level
  FROM employee
  WHERE emp_id = 100
  UNION ALL
  -- recursive case: select the manager of the previous employee
  SELECT e.emp_id, e.emp_name, e.mgr_id, a.level + 1 AS level
  FROM employee e
  JOIN ancestor a ON e.emp_id = a.mgr_id
)
-- select the CTE result
SELECT *
FROM ancestor;

Add your perspective

Bevan Ward

Principal Advisor Innovation - Data and Information Management at Rio Tinto
Report contribution
A simple example of a company structure can be written like this in SQL Server: declare @emp table (EmployeeID INT PRIMARY KEY,EmployeeName NVARCHAR(50), ManagerID INT) INSERT INTO @emp (EmployeeID, EmployeeName, ManagerID) VALUES (1, 'John', NULL),(2, 'Alice', 1),(3, 'Bob', 1),(4, 'Charlie', 2),(5, 'David', 2),(6, 'Eva', 3),(7, 'Frank', 3); WITH EmployeeHierarchyCTE AS ( SELECT EmployeeID,EmployeeName,ManagerID, 0 AS Level FROM @emp WHERE ManagerID IS NULL UNION ALL SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, eh.Level + 1 AS Level FROM @emp e INNER JOIN EmployeeHierarchyCTE eh ON e.ManagerID = eh.EmployeeID ) SELECT * FROM EmployeeHierarchyCTE; Note the anchor to head (managerID is null)

Like

3 How to control the recursion depth?

Sometimes, you may want to limit the number of iterations or the level of recursion in a recursive query. You can do this by using a WHERE clause or a MAXRECURSION option. A WHERE clause can filter out the rows that exceed a certain condition, such as level > 5. A MAXRECURSION option can specify the maximum number of recursions allowed, such as MAXRECURSION 10. If the recursion exceeds the limit, the query will return an error or a warning, depending on the database system.

Add your perspective

Bevan Ward

Principal Advisor Innovation - Data and Information Management at Rio Tinto
Report contribution
To set the depth of recursion in SQL Server there is the command OPTION (MAXRECURSION <number>) To enable bottomless set to 0

Like

4 How to order the recursive result?

One of the challenges of recursive queries is to order the result in a meaningful way. Since the UNION ALL operator does not guarantee any order, you may need to use an ORDER BY clause or a ranking function to sort the rows. An ORDER BY clause can sort the rows by one or more columns, such as level ASC or emp_name DESC. A ranking function can assign a rank or a row number to each row based on a partition and an order, such as ROW_NUMBER() OVER (PARTITION BY level ORDER BY emp_name). You can then use the rank or the row number to order the result.

Add your perspective

5 How to optimize the recursive performance?

Recursive queries can be expensive in terms of resources and time, especially if the recursion depth is high or the data size is large. To optimize the recursive performance, you can use some techniques such as indexing, filtering, caching, and partitioning. Indexing can speed up the join and the filter operations in the recursive case. Filtering can reduce the number of rows in the base case and the recursive case. Caching can store the intermediate results of the CTE in memory or disk. Partitioning can divide the data into smaller and independent subsets that can be processed in parallel.

Add your perspective

ABDUL QADEER

Data Analyst | BI DEVELOPER | POWER BI | SQL | SSIS | Python
Report contribution
Ensure that columns used in WHERE clauses are appropriately indexed to speed up the filtering process. And indexing columns which are involved in the JOIN conditions can significantly improve the performance of recursive queries.

Like
Bevan Ward

Principal Advisor Innovation - Data and Information Management at Rio Tinto
Report contribution
To optimise performance ensure there is an index on the on the join fields/foreign key. Also set the recursion depth to prevent it blowing out.

Like

6 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?

Add your perspective

Hoang Le

Technical Business Analyst | Data Analyst | Product Specialist | MBA, B.Eng in Computer Science
(edited)
Report contribution
Recursive query would be a very powerful tool for manipulating data where straightforward aggregation functions (e.g. sum, average, max) can't solve the need. Typical examples would be to find the hierarchy information in the same table (like the employee case shown in this article), or splitting the data records based on certain conditions (e.g. the continuous phone usage splitting into blocks of 15 minutes of the same user). Mastering this skill will help data analysts handle more complex data analysis scenarios.

Like

What are the steps to implement recursive queries in SQL?

1

2

3

4

5

6

1 What is a recursive query?

2 How to write a recursive CTE?

3 How to control the recursion depth?

4 How to order the recursive result?

5 How to optimize the recursive performance?

6 Here’s what else to consider

Database Development

Rate this article

Thanks for your feedback

More articles on Database Development

More relevant reading

What are the steps to implement recursive queries in SQL?

1

2

3

4

5

6

1 What is a recursive query?

2 How to write a recursive CTE?

3 How to control the recursion depth?

4 How to order the recursive result?

5 How to optimize the recursive performance?

6 Here’s what else to consider

Database Development

Rate this article

Thanks for your feedback

Explore Other Skills