For the first article on this blog, I'm going to talk about SQL. We're staying on topic with SQLable 🙂
The day I discovered CTEs (Common Table Expressions), more specifically recursive CTEs, I was blown away! When writing SQL queries, there comes a point when things start getting a little complicated. You start with a simple join, then add a subquery, another subquery inside the first one, and you end up with a query that's difficult to read and, above all, maintain. Fortunately, SQL provides a feature called CTEs.
A CTE allows you to define an intermediate result that you can then reuse in an SQL query. Let's see how this feature works and how it can make our lives easier.
Understanding Common Table Expressions (CTEs)
A CTE is a temporary table expression defined using the WITH keyword. It allows you to give a name to an SQL query and then use its result as if it were a regular table. Cool ^^
Let's take an example with an orders table containing orders:
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
amount DECIMAL(10, 2),
status VARCHAR(20)
);
Let's imagine that we want to retrieve orders with an amount greater than 100 euros.
Without a CTE, we could simply write:
SELECT *
FROM orders
WHERE amount > 100;
But let's now use a CTE:
WITH expensive_orders AS (
SELECT *
FROM orders
WHERE amount > 100
)
SELECT *
FROM expensive_orders;
The result is identical, but we've given a name to our intermediate query: expensive_orders. The general syntax is quite simple:
WITH cte_name AS (
SELECT ...
)
SELECT *
FROM cte_name;
A CTE is not a physical table: it is not permanently stored in the database. It is defined for the needs of the SQL statement that follows.
In this simple example, you can't really see the point, but when you imagine queries with several subqueries, you can clearly see what it brings in terms of readability. That's its main advantage over subqueries!
Declaring Multiple CTEs in a Single Query
It is possible to declare several CTEs in a single SQL statement. To do this, you simply need to separate them with commas.
Let's imagine that we want to analyze our customers' orders and calculate their total amount. We can start by retrieving completed orders, then calculate the revenue for each customer:
WITH completed_orders AS (
SELECT *
FROM orders
WHERE status = 'completed'
),
customer_totals AS (
SELECT
customer_id,
SUM(amount) AS total_amount
FROM completed_orders
GROUP BY customer_id
)
SELECT *
FROM customer_totals;
Here, we have two CTEs:
completed_ordersretrieves only completed orders.customer_totalscalculates the total order amount for each customer.
The second CTE directly uses the result of the first one! This is particularly useful for breaking down a complex query into several smaller, independent steps.
Chaining Multiple Processing Steps
CTEs become even more interesting when you want to perform several successive operations. Let's take a slightly more concrete example: identifying customers whose total order amount exceeds 500 euros.
We will proceed in three steps:
- Retrieve completed orders.
- Calculate the total amount for each customer.
- Filter customers whose total exceeds 500 euros.
Here's the complete query:
WITH completed_orders AS (
SELECT *
FROM orders
WHERE status = 'completed'
),
customer_totals AS (
SELECT
customer_id,
SUM(amount) AS total_amount
FROM completed_orders
GROUP BY customer_id
),
important_customers AS (
SELECT *
FROM customer_totals
WHERE total_amount > 500
)
SELECT *
FROM important_customers
ORDER BY total_amount DESC;
Each CTE represents one processing step. We could perfectly well write this query using nested subqueries, but the result would be much less pleasant to read.
It's also an advantage when you have to modify a query a few weeks later: it's easier to understand what each part does.
Understanding recursive CTEs
Ahhhh recursive CTEs, they're really powerful! A recursive CTE can reference itself. This is particularly useful for navigating hierarchical structures, such as an organizational chart, a category tree or a list of parent-child relationships.
Let's take a table representing a category hierarchy:
CREATE TABLE categories (
id INTEGER PRIMARY KEY,
name VARCHAR(100),
parent_id INTEGER
);
Here is some data:
INSERT INTO categories (id, name, parent_id) VALUES
(1, 'IT', NULL),
(2, 'Development', 1),
(3, 'Databases', 2),
(4, 'SQL', 3),
(5, 'Web', 2);
Here we have a hierarchy:
IT
└── Development
├── Databases
│ └── SQL
└── Web
How can we retrieve all the categories belonging to the "IT" branch? A recursive CTE allows us to automatically navigate through the different levels:
WITH RECURSIVE category_tree AS (
-- Starting point
SELECT
id,
name,
parent_id,
0 AS level
FROM categories
WHERE id = 1
UNION ALL
-- Find children
SELECT
c.id,
c.name,
c.parent_id,
ct.level + 1
FROM categories c
INNER JOIN category_tree ct
ON c.parent_id = ct.id
)
SELECT *
FROM category_tree
ORDER BY level, id;
The first part of the CTE defines the starting point: here, the "IT" category.
The second part searches for child categories using the result of the previous iteration.
The process repeats until there are no more children to find.
The level column also allows us to know the depth of each category in the tree.
Be careful with cycles in hierarchical data: a poorly constructed parent-child relationship can cause infinite recursion, ouch. You therefore need to plan for a cycle detection strategy or a depth limit when the data is not under control.
Compatibility with SQLite, MySQL and PostgreSQL
Good news, the three main SQL database engines we regularly use support CTEs.
| Feature | SQLite | MySQL | PostgreSQL |
|---|---|---|---|
| Standard CTEs | Yes | Yes (8.0+) | Yes |
| Multiple CTEs | Yes | Yes | Yes |
| Recursive CTEs | Yes | Yes (8.0+) | Yes |
RECURSIVE keyword | Yes | Yes | Yes |
However, there are some differences in behavior and optimization depending on the database engine. For example, PostgreSQL and MySQL can treat certain CTEs as materialized intermediate results or inline them into the main query, depending on the conditions and available optimization options.
So you shouldn't consider a CTE to be a temporary table that is systematically stored in memory or on disk.
Conclusion
CTEs are a feature I find particularly useful for writing more readable and better organized SQL queries.
They allow you to replace certain nested subqueries, break down processing into several steps and even navigate hierarchical structures thanks to recursion. And this recursion allows you to perform countless queries with a single SQL statement!
Once you've discovered CTEs, there's no going back!