SQL

Find Gaps in an ID Sequence Using Recursive CTE

Efficiently locate missing identifiers or gaps within a numeric sequence in your SQL table using a recursive Common Table Expression (CTE) for data integrity.

WITH RECURSIVE NumberSeries AS (
  SELECT MIN(id) AS n FROM YourTable
  UNION ALL
  SELECT n + 1 FROM NumberSeries WHERE n < (SELECT MAX(id) FROM YourTable)
)
SELECT
  ns.n AS missing_id
FROM
  NumberSeries ns
LEFT JOIN
  YourTable yt ON ns.n = yt.id
WHERE
  yt.id IS NULL
ORDER BY
  missing_id;

How it works: This SQL snippet uses a recursive Common Table Expression (CTE) to first generate a complete sequence of numbers from the minimum to the maximum `id` found in `YourTable`. It then performs a `LEFT JOIN` with `YourTable` itself. By filtering `WHERE yt.id IS NULL`, it effectively identifies and returns all numbers from the generated series that do not have a corresponding `id` in `YourTable`, thus revealing gaps in the sequence.

Find Gaps in an ID Sequence Using Recursive CTE

Need help integrating this into your project?