Recursive CTE - Date Series

Recursive CTE - Date Series is a SQL statement in the CTE (WITH) category. Generate a continuous series of dates between two endpoints without a calendar table. The syntax is WITH RECURSIVE dates AS (SELECT DATE('start') AS d UNION ALL SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM dates WHERE d < 'end') SELECT * FROM dates;. It returns date series. A typical example: WITH RECURSIVE dates AS ( SELECT '2026-01-01' AS dt UNION ALL SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM dates WHERE dt < '2026-12-31' ) SELECT dt FROM dates; -- All 365 days of 2026 -- Join with data to find gaps: SELECT d.dt, COUNT(o.id) AS orders FROM dates d LEFT JOIN orders o ON DATE(o.created_at) = d.dt GROUP BY d.dt ORDER BY d.dt; -- Dates with 0 orders = gaps --… A close relative is WITH (CTE), which common Table Expression — define a named temporary result set that can be referenced in the main query.