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. Maintaining such a long SQL statement can be a developer’s nightmare.

To avoid such a long SQL statement, you can simplify queries by using Views or cache intermediate query results by using Temporary tables.

This document introduces the Common Table Expression (CTE) syntax in TiDB, which is a more convenient way to reuse query results.

Since TiDB v5.1, TiDB supports the CTE of the ANSI SQL99 standard and recursion. With CTE, you can write SQL statements for complex application logic more efficiently and maintain the code much easier.

Basic use

A Common Table Expression (CTE) is a temporary result set that can be referred to multiple times within a SQL statement to improve the statement readability and execution efficiency. You can apply the WITH statement to use CTE.

Common Table Expressions can be classified into two types: non-recursive CTE and recursive CTE.

Non-recursive CTE

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

  1. WITH <query_name> AS (
  2. <query_definition>
  3. )
  4. 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:

  • SQL
  • Java

Change the statement in temporary tables to the following:

  1. WITH top_50_eldest_authors_cte AS (
  2. SELECT a.id, a.name, (IFNULL(a.death_year, YEAR(NOW())) - a.birth_year) AS age
  3. FROM authors a
  4. ORDER BY age DESC
  5. LIMIT 50
  6. )
  7. SELECT
  8. ANY_VALUE(ta.id) AS author_id,
  9. ANY_VALUE(ta.age) AS author_age,
  10. ANY_VALUE(ta.name) AS author_name,
  11. COUNT(*) AS books
  12. FROM top_50_eldest_authors_cte ta
  13. LEFT JOIN book_authors ba ON ta.id = ba.author_id
  14. GROUP BY ta.id;

The result is as follows:

  1. +------------+------------+---------------------+-------+
  2. | author_id | author_age | author_name | books |
  3. +------------+------------+---------------------+-------+
  4. | 1238393239 | 80 | Araceli Purdy | 1 |
  5. | 817764631 | 80 | Ivory Davis | 3 |
  6. | 3093759193 | 80 | Lysanne Harris | 1 |
  7. | 2299112019 | 80 | Ray Macejkovic | 4 |
  8. ...
  9. +------------+------------+---------------------+-------+
  10. 50 rows in set (0.01 sec)
  1. public List<Author> getTop50EldestAuthorInfoByCTE() throws SQLException {
  2. List<Author> authors = new ArrayList<>();
  3. try (Connection conn = ds.getConnection()) {
  4. Statement stmt = conn.createStatement();
  5. ResultSet rs = stmt.executeQuery("""
  6. WITH top_50_eldest_authors_cte AS (
  7. SELECT a.id, a.name, (IFNULL(a.death_year, YEAR(NOW())) - a.birth_year) AS age
  8. FROM authors a
  9. ORDER BY age DESC
  10. LIMIT 50
  11. )
  12. SELECT
  13. ANY_VALUE(ta.id) AS author_id,
  14. ANY_VALUE(ta.name) AS author_name,
  15. ANY_VALUE(ta.age) AS author_age,
  16. COUNT(*) AS books
  17. FROM top_50_eldest_authors_cte ta
  18. LEFT JOIN book_authors ba ON ta.id = ba.author_id
  19. GROUP BY ta.id;
  20. """);
  21. while (rs.next()) {
  22. Author author = new Author();
  23. author.setId(rs.getLong("author_id"));
  24. author.setName(rs.getString("author_name"));
  25. author.setAge(rs.getShort("author_age"));
  26. author.setBooks(rs.getInt("books"));
  27. authors.add(author);
  28. }
  29. }
  30. return authors;
  31. }

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:

  1. WITH books_authored_by_rm AS (
  2. SELECT *
  3. FROM books b
  4. LEFT JOIN book_authors ba ON b.id = ba.book_id
  5. WHERE author_id = 2299112019
  6. ), books_with_average_ratings AS (
  7. SELECT
  8. b.id AS book_id,
  9. AVG(r.score) AS average_rating
  10. FROM books_authored_by_rm b
  11. LEFT JOIN ratings r ON b.id = r.book_id
  12. GROUP BY b.id
  13. ), books_with_orders AS (
  14. SELECT
  15. b.id AS book_id,
  16. COUNT(*) AS orders
  17. FROM books_authored_by_rm b
  18. LEFT JOIN orders o ON b.id = o.book_id
  19. GROUP BY b.id
  20. )
  21. SELECT
  22. b.id AS `book_id`,
  23. b.title AS `book_title`,
  24. br.average_rating AS `average_rating`,
  25. bo.orders AS `orders`
  26. FROM
  27. books_authored_by_rm b
  28. LEFT JOIN books_with_average_ratings br ON b.id = br.book_id
  29. LEFT JOIN books_with_orders bo ON b.id = bo.book_id
  30. ;

The result is as follows:

  1. +------------+-------------------------+----------------+--------+
  2. | book_id | book_title | average_rating | orders |
  3. +------------+-------------------------+----------------+--------+
  4. | 481008467 | The Documentary of goat | 2.0000 | 16 |
  5. | 2224531102 | Brandt Skiles | 2.7143 | 17 |
  6. | 2641301356 | Sheridan Bashirian | 2.4211 | 12 |
  7. | 4154439164 | Karson Streich | 2.5833 | 19 |
  8. +------------+-------------------------+----------------+--------+
  9. 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.

Common Table Expression - 图1

Tip

If the efficiency of the default CTE queries is not good, you can use the MERGE() hint to expand the CTE subquery to the outer query to improve the efficiency.

Recursive CTE

Recursive CTE can be defined using the following syntax:

  1. WITH RECURSIVE <query_name> AS (
  2. <query_definition>
  3. )
  4. SELECT ... FROM <query_name>;

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

  1. WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
  2. (
  3. SELECT 1, 0, 1
  4. UNION ALL
  5. SELECT n + 1, next_fib_n, fib_n + next_fib_n FROM fibonacci WHERE n < 10
  6. )
  7. SELECT * FROM fibonacci;

The result is as follows:

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

Read more