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_orders retrieves only completed orders.
  • customer_totals calculates 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:

  1. Retrieve completed orders.
  2. Calculate the total amount for each customer.
  3. 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.

FeatureSQLiteMySQLPostgreSQL
Standard CTEsYesYes (8.0+)Yes
Multiple CTEsYesYesYes
Recursive CTEsYesYes (8.0+)Yes
RECURSIVE keywordYesYesYes

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!

SQL CTE: Simplifying complex queries with WITH