Write a query to find gaps in a sequence of numbers (missing IDs).

Finding Gaps in a Sequence (Missing IDs) in SQL

Table

CREATE TABLE Orders (
    OrderId INT PRIMARY KEY,
    Customer VARCHAR(100)
);

INSERT INTO Orders (OrderId, Customer) VALUES
(1, 'A'), (2, 'B'), (3, 'C'),
(6, 'D'), (7, 'E'),
(10, 'F'),
(14, 'G'), (15, 'H');

Missing IDs: 4, 5, 8, 9, 11, 12, 13.

Query 1: gap ranges (start and end of each gap)

Uses LEAD to compare each ID with the next one.

WITH NextId AS (
    SELECT
        OrderId,
        LEAD(OrderId) OVER (ORDER BY OrderId) AS NextOrderId
    FROM Orders
)
SELECT
    OrderId + 1         AS GapStart,
    NextOrderId - 1     AS GapEnd,
    NextOrderId - OrderId - 1 AS MissingCount
FROM NextId
WHERE NextOrderId - OrderId > 1
ORDER BY GapStart;

Result

GapStart	GapEnd	MissingCount
4	5	2
8	9	2
11	13	3

This is the fastest and most compact approach. It reads the table once and returns one row per gap.

Query 2: every missing ID listed individually

SQL Server / Azure SQL (recursive CTE, no numbers table needed)

WITH Bounds AS (
    SELECT MIN(OrderId) AS MinId, MAX(OrderId) AS MaxId FROM Orders
),
AllIds AS (
    SELECT MinId AS Id, MaxId FROM Bounds
    UNION ALL
    SELECT Id + 1, MaxId FROM AllIds WHERE Id < MaxId
)
SELECT a.Id AS MissingId
FROM AllIds a
LEFT JOIN Orders o ON o.OrderId = a.Id
WHERE o.OrderId IS NULL
ORDER BY a.Id
OPTION (MAXRECURSION 0);

SQL Server 2022 / Azure SQL (GENERATE_SERIES)

SELECT s.value AS MissingId
FROM GENERATE_SERIES(
        (SELECT MIN(OrderId) FROM Orders),
        (SELECT MAX(OrderId) FROM Orders)) s
LEFT JOIN Orders o ON o.OrderId = s.value
WHERE o.OrderId IS NULL
ORDER BY s.value;

PostgreSQL

SELECT g AS missing_id
FROM generate_series(
        (SELECT MIN(order_id) FROM orders),
        (SELECT MAX(order_id) FROM orders)) AS g
LEFT JOIN orders o ON o.order_id = g
WHERE o.order_id IS NULL
ORDER BY g;

Result

MissingId
4
5
8
9
11
12
13

Query 3: gaps and islands (alternative using ROW_NUMBER)

Shows the present ranges; the gaps are the spaces between them.

WITH Islands AS (
    SELECT
        MIN(OrderId) AS IslandStart,
        MAX(OrderId) AS IslandEnd
    FROM (
        SELECT OrderId,
               OrderId - ROW_NUMBER() OVER (ORDER BY OrderId) AS grp
        FROM Orders
    ) t
    GROUP BY grp
)
SELECT IslandStart, IslandEnd
FROM Islands
ORDER BY IslandStart;

Result

IslandStart	IslandEnd
1	3
6	7
10	10
14	15

Query 4: gaps without window functions (older databases)

SELECT
    o.OrderId + 1 AS GapStart,
    (SELECT MIN(o2.OrderId) FROM Orders o2 WHERE o2.OrderId > o.OrderId) - 1 AS GapEnd
FROM Orders o
WHERE NOT EXISTS (SELECT 1 FROM Orders o2 WHERE o2.OrderId = o.OrderId + 1)
  AND o.OrderId < (SELECT MAX(OrderId) FROM Orders)
ORDER BY GapStart;

Slower, since it runs subqueries per row, but it works on any database.

Query 5: gaps per group (for example, per customer or per year)

WITH NextId AS (
    SELECT
        Region,
        InvoiceNo,
        LEAD(InvoiceNo) OVER (PARTITION BY Region ORDER BY InvoiceNo) AS NextInvoiceNo
    FROM Invoices
)
SELECT Region,
       InvoiceNo + 1     AS GapStart,
       NextInvoiceNo - 1 AS GapEnd
FROM NextId
WHERE NextInvoiceNo - InvoiceNo > 1
ORDER BY Region, GapStart;

PARTITION BY restarts the check for each group, which is what you want when every group has its own numbering.

Query 6: check whether the sequence starts at 1

The queries above only find gaps between existing IDs. To catch missing IDs at the beginning:

SELECT 1 AS GapStart, MIN(OrderId) - 1 AS GapEnd
FROM Orders
HAVING MIN(OrderId) > 1;

For missing IDs after the last one, you need an expected maximum from somewhere else (a counter, a config value, or a parent table), since the table itself can't know.

Details

• LEAD approach (Query 1) is the best default: one pass, one row per gap, easy to read.
• Listing each missing ID (Query 2) needs a generated range of numbers. The recursive CTE works anywhere; GENERATE_SERIES is simpler where available (SQL Server 2022+, Azure SQL, PostgreSQL).
• Recursion limit: SQL Server stops at 100 levels by default, so use OPTION (MAXRECURSION 0) for large ranges. For millions of IDs, use a numbers (tally) table instead.
• Duplicates: if the column isn't unique, add SELECT DISTINCT in the first CTE, otherwise LEAD returns the same value and hides gaps.
• Identity columns create gaps naturally (rolled-back inserts, deletes, failed transactions), so gaps there are usually normal. Gaps matter for things like invoice numbers, where auditors expect a continuous sequence.
• Sequence start and end: decide whether leading and trailing gaps count (Query 6).

Supporting index

-- Usually the primary key already covers this. For a non-key column:
CREATE INDEX IX_Orders_OrderId ON Orders (OrderId);

An ordered index lets LEAD and ROW_NUMBER read the rows without a sort.

Compatibility

LEAD and ROW_NUMBER work in Azure SQL, SQL Server 2012+, PostgreSQL, MySQL 8.0+, Oracle, and SQLite 3.25+. GENERATE_SERIES requires SQL Server 2022+ or PostgreSQL. In MySQL, use a recursive CTE with WITH RECURSIVE for the full list of missing IDs.