Recursive CTEs in MySQL

In SQL, Common Table Expressions (CTEs) are often used to combine multiple SQL queries into a single result set. However, there's a lesser-known type of CTE called a Recursive CTE, which can be used for generating data and manipulating hierarchical data structures.

In this video, we'll explore Recursive CTEs and how they can be used to build hierarchical lists, tree structures, and other cool things in MySQL. We'll also dive into creating a sample Recursive CTE and explore how to use it to fill in gaps in data.

Getting started with recursive CTEs

To get started with Recursive CTEs, it's important first to understand the basics of CTEs themselves. A CTE is essentially a temporary result set that can be referred to within a SQL statement. CTEs allow you to break complex queries into more manageable, modular components.

Recursive CTEs, on the other hand, refer to themselves repeatedly to build up data. This simple fact enables Recursive CTEs to perform tasks that would be impossible with regular CTEs.

Building a sample recursive CTE

Let's look at an example of how to use a Recursive CTE to build a simple list of numbers in SQL. We'll create a table called "numbers" and define our Recursive CTE to generate a sequence of numbers from 1 to 10.

WITH RECURSIVE numbers AS (
  SELECT 1 AS n -- Initial Condition
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 10 -- Recursive Condition
)

SELECT * FROM numbers;

Here's what's happening in this code:

• We define a CTE called numbers using the WITH keyword and specify the RECURSIVE modifier.
• We define our initial condition, which selects the number 1 and assigns it the alias n.
• We define our recursive condition, which selects the value of n + 1 from the table numbers. This incrementally generates the sequence of numbers from 1 to 10.

If we run this code, we get a list of numbers from 1 to 10:

 n
---
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10
(10 rows)

Working with dates in recursive CTEs

Recursive CTEs are not limited to generating lists of numbers. They can also work with dates and timestamps by generating sequences of dates.

Here's an example of how to use a Recursive CTE to generate a sequence of dates throughout the year 2023:

WITH RECURSIVE all_dates AS (
  SELECT '2023-01-01' AS dt -- Initial Condition
  UNION ALL
  SELECT dt + INTERVAL 1 DAY FROM all_dates WHERE dt < '2023-12-31' -- Recursive Condition
)
SELECT * FROM all_dates;

If we run this code, we get a sequence of dates from January 1, 2023, to December 31, 2023:

    dt
------------
 2023-01-01
 2023-01-02
 2023-01-03
 2023-01-04
 2023-01-05
 ...

Using Recursive CTEs, we can generate date sequences and use them to fill in gaps in our data. Let's see how we can use this generated sequence to fill in dates with missing data.

Filling in gaps in data

One of the benefits of Recursive CTEs is that they can be used to fill in gaps in data. Let's say we have a table of payments with missing data on certain dates:

payment_date | amount
-------------+--------
 2023-01-02  |  23.51
 2023-01-03  | 150.23
 2023-01-04  | 120.00
 ...

We can fill in these missing dates using a Recursive CTE. Here's an example of how it might look:

WITH RECURSIVE all_dates AS (
  SELECT '2023-01-01' AS dt -- Initial Condition
  UNION ALL
  SELECT dt + INTERVAL 1 DAY FROM all_dates WHERE dt < '2023-12-31' -- Recursive Condition
)

SELECT
  dt,
  sum(ammount)
FROM
  all_dates
  LEFT JOIN payments ON all_dates.dt = payments.date;

Here's what's happening in this code:

• We define a CTE called "all_dates" using the WITH keyword and specify the RECURSIVE modifier. This generates a sequence of dates throughout the year 2023.
• We join the "all_dates" CTE to the "payments" table on their date columns.
• We select all dates from "all_dates" and the amount from "payments."

Now, we have a complete list of dates with the total amount for each date, even those without payment records:

    date     | amount
-------------+--------
 2023-01-01  |   NULL
 2023-01-02  |  23.51
 2023-01-03  | 150.23
 2023-01-04  | 120.00

Conclusion

Recursive CTEs can be used to generate data or work with data that you already have. They are a powerful tool to fill gaps in missing data or generate hierarchical data structures.