SQL
Identify Missing Numbers/Gaps in a Sequence of IDs
Learn to find gaps or missing IDs in a sequential column (e.g., order_id, user_id) using SQL queries, crucial for data integrity checks.
-- Example: Find missing 'id' values in a table 'items'
-- Assumes 'id' is a sequential integer column.
SELECT
s.id + 1 AS gap_start,
MIN(t.id) - 1 AS gap_end
FROM items s
JOIN items t ON t.id > s.id
LEFT JOIN items missing_check ON missing_check.id = s.id + 1
WHERE missing_check.id IS NULL
GROUP BY s.id
HAVING MIN(t.id) > s.id + 1;
-- A simpler approach using LEAD() (PostgreSQL, SQL Server, MySQL 8.0+):
-- SELECT
-- id + 1 AS gap_start,
-- next_id - 1 AS gap_end
-- FROM (
-- SELECT
-- id,
-- LEAD(id, 1) OVER (ORDER BY id) as next_id
-- FROM items
-- ) AS numbered_items
-- WHERE next_id IS NOT NULL AND next_id > id + 1;
How it works: This snippet provides methods to identify missing numbers or gaps in a sequential ID column. The first approach uses a self-join with a `LEFT JOIN` and `WHERE missing_check.id IS NULL` to find where the next expected ID is absent. It then groups and filters to pinpoint the start and end of the gaps. The second, simpler approach (for databases supporting window functions) uses `LEAD()` to get the next ID in the sequence and then checks if `next_id` is greater than `id + 1`, indicating a gap. This is vital for auditing sequences like order numbers or user IDs.