Simplify SQL Queries with Common Table Expression

In some transaction scenarios, due to application complexity, you might need to write a single SQL statement of up to 2,000 lines. The statement probably contains a lot of aggregations and multi-level subquery nesting. To avoid such a long SQL statement, you can simplify queries by using Views or cache intermediate query results by using Temporary tables. In this post, we will introduce the Common Table Expression (CTE) syntax, which is a more convenient way to reuse query results.

Understanding Common Table Expression

Common Table Expressions, or CTEs, are a powerful SQL feature that allows you to create temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs make your queries more readable and maintainable by breaking them into simpler, modular components.

A CTE is defined using the WITH clause followed by the CTE name and the query it encapsulates. Once defined, it acts as a temporary table that you can use in your main query. This temporary table is only available within the scope of the query where it is defined and disappears once the query is executed.

Benefits of Using CTEs

Simplifying Complex Queries

CTEs can take complex queries and break them down into simpler, more understandable parts. This not only makes your SQL statements more readable but also easier to debug and maintain.

Improving Performance

By structuring queries more efficiently and allowing for recursive operations, CTEs can also lead to performance improvements, especially in cases where complex joins and subqueries are involved.

Practical Examples of CTEs

Let’s look at some practical examples of how CTEs can be used, including both non-recursive CTE and recursive CTE. As a distributed SQL database for modern applications, TiDB supports the CTE of the ANSI SQL99 standard and recursion. TiDB Serverless delivers a serverless database in seconds while you only pay for the data processing. In the following, we will introduce these two types in TiDB.

Non-recursive CTE

Non-recursive CTE can be defined using the following syntax:

WITH <query_name> AS (
    <query_definition>
)
SELECT ... FROM <query_name>;

For example, if you want to know how many books each of the 50 oldest authors have written, take the following steps:

WITH top_50_eldest_authors_cte AS (
    SELECT a.id, a.name, (IFNULL(a.death_year, YEAR(NOW())) - a.birth_year) AS age
    FROM authors a
    ORDER BY age DESC
    LIMIT 50
)
SELECT
    ANY_VALUE(ta.id) AS author_id,
    ANY_VALUE(ta.age) AS author_age,
    ANY_VALUE(ta.name) AS author_name,
    COUNT(*) AS books
FROM top_50_eldest_authors_cte ta
LEFT JOIN book_authors ba ON ta.id = ba.author_id
GROUP BY ta.id;

The result is as follows:

+------------+------------+---------------------+-------+
| author_id  | author_age | author_name         | books |
+------------+------------+---------------------+-------+
| 1238393239 |         80 | Araceli Purdy       |     1 |
|  817764631 |         80 | Ivory Davis         |     3 |
| 3093759193 |         80 | Lysanne Harris      |     1 |
| 2299112019 |         80 | Ray Macejkovic      |     4 |
...
+------------+------------+---------------------+-------+
50 rows in set (0.01 sec)

It can be found that the author “Ray Macejkovic” wrote 4 books. With the CTE query, you can further get the order and rating information of these 4 books as follows:

WITH books_authored_by_rm AS (
    SELECT *
    FROM books b
    LEFT JOIN book_authors ba ON b.id = ba.book_id
    WHERE author_id = 2299112019
), books_with_average_ratings AS (
    SELECT
        b.id AS book_id,
        AVG(r.score) AS average_rating
    FROM books_authored_by_rm b
    LEFT JOIN ratings r ON b.id = r.book_id
    GROUP BY b.id
), books_with_orders AS (
    SELECT
        b.id AS book_id,
        COUNT(*) AS orders
    FROM books_authored_by_rm b
    LEFT JOIN orders o ON b.id = o.book_id
    GROUP BY b.id
)
SELECT
    b.id AS `book_id`,
    b.title AS `book_title`,
    br.average_rating AS `average_rating`,
    bo.orders AS `orders`
FROM
    books_authored_by_rm b
    LEFT JOIN books_with_average_ratings br ON b.id = br.book_id
    LEFT JOIN books_with_orders bo ON b.id = bo.book_id
;

The result is as follows:

+------------+-------------------------+----------------+--------+
| book_id    | book_title              | average_rating | orders |
+------------+-------------------------+----------------+--------+
|  481008467 | The Documentary of goat |         2.0000 |     16 |
| 2224531102 | Brandt Skiles           |         2.7143 |     17 |
| 2641301356 | Sheridan Bashirian      |         2.4211 |     12 |
| 4154439164 | Karson Streich          |         2.5833 |     19 |
+------------+-------------------------+----------------+--------+
4 rows in set (0.06 sec)

Three CTE blocks, which are separated by ,, are defined in this SQL statement.

First, check out the books written by the author (ID is 2299112019) in the CTE block books_authored_by_rm. Then find the average rating and order for these books respectively in books_with_average_ratings and books_with_orders. Finally, aggregate the results by the JOIN statement.

Note that the query in books_authored_by_rm executes only once, and then TiDB creates a temporary space to cache its result. When the queries in books_with_average_ratings and books_with_orders refer to books_authored_by_rm, TiDB gets its result directly from this temporary space.

Recursive CTE

Recursive CTE can be defined using the following syntax:

WITH RECURSIVE <query_name> AS (
    <query_definition>
)
SELECT ... FROM <query_name>;

A classic example is to generate a set of Fibonacci numbers with recursive CTE:

WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
(
  SELECT 1, 0, 1
  UNION ALL
  SELECT n + 1, next_fib_n, fib_n + next_fib_n FROM fibonacci WHERE n < 10
)
SELECT * FROM fibonacci;

The result is as follows:

+------+-------+------------+
| n    | fib_n | next_fib_n |
+------+-------+------------+
|    1 |     0 |          1 |
|    2 |     1 |          1 |
|    3 |     1 |          2 |
|    4 |     2 |          3 |
|    5 |     3 |          5 |
|    6 |     5 |          8 |
|    7 |     8 |         13 |
|    8 |    13 |         21 |
|    9 |    21 |         34 |
|   10 |    34 |         55 |
+------+-------+------------+
10 rows in set (0.00 sec)

Conclusion

Common Table Expressions offer SQL users an elegant way to make their complex queries more structured, readable, and often more performance-efficient. Whether you’re handling vast sets of hierarchical data or simply looking to write cleaner code, CTEs can be an excellent addition to your SQL toolkit.

For users of TiDB, an open-source distributed SQL database, leveraging CTEs can further amplify its inherent scalability and performance benefits. Both non-recursive and recursive CTEs can be executed effortlessly on TiDB, giving developers the flexibility they need to tackle complex data challenges head-on.

Interested in exploring CTEs or other advanced SQL features on a profoundly scalable and reliable database? Consider trying Common Table Expressions on TiDB Serverless, where you can enjoy the flexibility of serverless architecture combined with the powerful feature set of TiDB.

Sign Up for Free