intro
Today, we’re digging into SQL recursive expressions. What do they mean and how and when should you use them? Figure that out here.
If you’ve ever worked on a project, you’ve certainly executed SQL queries. The SQL queries that your application had (and still has) to execute depends on your specific use case and requirements.
Many SQL queries are “vanilla” enough, meaning that these kinds of queries usually don’t do much other than insert, update, select, or delete data as requested. And that’s not a bad thing — after all, you only want your database to perform tasks that are necessary to be performed, right?
You want that because SQL queries don’t walk alone. They are composed of many internal tasks that all need to be executed in unison for your database to give your SQL query an OK sign to execute. Similarly, not all queries are the same — such is the truth about an SQL recursive query, too.
What Is an SQL Recursive Query?
A SQL recursive query is an SQL query that:
The recursive query should begin with an anchor query that selects the rows your database has to work with. Afterward, you should fetch related data with a recursive query that runs until a condition is no longer satisfied.
In other words, in practice, a SQL recursive query would look something along those lines:
1
WITH RECURSIVE cte_example
2
AS (
3
-- Base (anchor) query here
4
...
5
UNION ALL
6
-- Recursive query here
7
...
8
)
9
SELECT * FROM cte_example;
As you can see, the SQL recursive query requires an SQL UNION ALL operator. That works just like UNION but it keeps duplicates when combining result sets. Learn more in our guide on how to use a recursive CTE in SQL.
An SQL Recursive Query in Practice
A perfect example of a recurring CTE would be when counting rows until a certain row number is reached — in this example, we count every third number until we reach the number 100:
1
WITH RECURSIVE counting_cte(n)
2
AS (
3
SELECT 1
4
UNION ALL
5
SELECT n + 3
6
FROM counting_cte
7
WHERE n < 100
8
)
9
SELECT n
10
FROM counting_cte;
Execute it in a visual database client like DbVisualizer, and you will get:
Once our SQL query reaches the number 100, it stops. Easy enough, right?
Here, SELECT 1 is the so-called “anchor query” that simply returns “1” as the base result.
SELECT n + 3 FROM counting_cte WHERE n < 100 is the SQL recursive query because it performs a recursive action: it instructs the database to increment a number starting from 1 to 100, skipping every 3 numbers in the process. I’ve emboldened the **recursive member** (this part of the query tells the database what to accomplish), and the WHERE clause is the termination condition — when this condition is reached, the query terminates. Cool, isn’t it?
Recursive Query Use Cases
What you’ve seen above is a basic example of a recursive SQL query. A recursive query isn’t a necessity all the time — it’s only used once we want to complete a task that can make use of a recurring SQL statement to be completed.
Also, not all recursive queries will look as simple as the one above — some (or, perhaps, even the majority, depending on your use case) will come with JOIN queries that need to be accounted for properly in terms of performance too. Your SQL recursive query may, at times, also look like so:
1
WITH RECURSIVE EmployeeHierarchy AS (
2
-- Anchor SQL query
3
SELECT employee_id, employee_name, manager_id, 0 AS level
4
FROM employees
5
WHERE manager_id IS NULL
6
UNION ALL
7
-- Recursive SQL quer
8
SELECT e.employee_id, e.employee_name, e.manager_id, eh.level + 1
9
FROM employees e
10
INNER JOIN
11
EmployeeHierarchy eh ON e.manager_id = eh.employee_id -- Recursive member
12
)
13
SELECT employee_id, employee_name, manager_id, level FROM EmployeeHierarchy
14
ORDER BY level, employee_id;
The query above would return:
This query is rather complex, but it is a SQL recursive query you would most likely see in the wild.
Summary
An SQL recursive query is an SQL query within a CTE — this kind of SQL query is repeated until a certain condition is reached, then results are returned.
A recursive query can be rather simple, or rather complex — it all depends on your use case.
We hope that this blog has been informational and useful, consider following our TheTable blog to learn more information about databases and their functionality, and until next time.
FAQ
What is an SQL recursive query?
A SQL recursive query is an SQL query run by your database until it no longer satisfies a specific condition (e.g. until auto-incrementing numbers are less than 100), and once it no longer satisfies the condition, it outputs results.
When to use an SQL recursive query?
An SQL recursive query should be used whenever you have a task that necessitates a “loop through data” to be completed (e.g. the same query running repeatedly until a condition is met/no longer met.)
Where can I learn more about database performance?
Consider reading blogs, attending conferences or workshops, or reading books. The book “Hacking MySQL: Breaking, Optimizing, and Securing MySQL for Your Use Case” would be a great place to start to both novices and experienced developers alike as it will not only tell you how to optimize your database for high performance but walk you through the things you’re doing wrong to necessitate optimization in the first place.
