Finding gaps in sequences in SQL

Introduction

Sometimes, you want to find missing numbers in a list that should be in order. This helps to spot gaps or missing data.

Checking missing invoice numbers in a billing system.

Finding absent seat numbers in a theater booking list.

Detecting missing order IDs in a sales database.

Verifying continuous numbering in employee IDs.

Syntax

SQL

SELECT current_value + 1 AS gap_start, next_value - 1 AS gap_end
FROM (
  SELECT value AS current_value, LEAD(value) OVER (ORDER BY value) AS next_value
  FROM your_table
) sub
WHERE next_value > current_value + 1;

LEAD() looks at the next row's value in order.

This query finds where the next number is more than 1 greater than the current, showing gaps.

Examples

Finds missing numbers between rows in the 'numbers' table.

SQL

SELECT current_num + 1 AS gap_start, next_num - 1 AS gap_end
FROM (
  SELECT num AS current_num, LEAD(num) OVER (ORDER BY num) AS next_num
  FROM numbers
) t
WHERE next_num > current_num + 1;

Detects missing employee IDs in the 'employees' table.

SQL

SELECT id + 1 AS missing_id
FROM (
  SELECT id, LEAD(id) OVER (ORDER BY id) AS next_id
  FROM employees
) e
WHERE next_id > id + 1;

Sample Program

This example finds gaps in the sequence of numbers 1,2,3,7,8,10. It shows missing numbers between these.

SQL

WITH numbers(num) AS (
  VALUES (1), (2), (3), (7), (8), (10)
)
SELECT num + 1 AS gap_start, next_num - 1 AS gap_end
FROM (
  SELECT num, LEAD(num) OVER (ORDER BY num) AS next_num
  FROM numbers
) sub
WHERE next_num > num + 1;

Important Notes

If the sequence has no gaps, the query returns no rows.

Make sure the column you check is numeric and sorted properly.

Summary

Use LEAD() to compare each number with the next one.

Find gaps where the next number is more than 1 greater.

This helps find missing or skipped numbers in sequences.