Find Gaps In An Integer Sequence

Generate the expected numbers and left join to the existing table to find missing ids or sequence values.

Created May 6, 2026 Last updated May 6, 2026 5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Find missing ticket numbers from 1 through 6

The CTE generates the expected numbers 1 through 6. The left join finds which expected numbers have no matching ticket row, leaving 3 and 5 as the gaps.

MySQL MariaDB PostgreSQL SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE tickets (ticket_no INT);

INSERT INTO
  tickets (ticket_no)
VALUES
  (1),
  (2),
  (4),
  (6);

Validated query

SQL

WITH RECURSIVE
  expected (n) AS (
    SELECT
      1
    UNION ALL
    SELECT
      n + 1
    FROM
      expected
    WHERE
      n < 6
  )
SELECT
  expected.n AS missing_ticket_no
FROM
  expected
  LEFT JOIN tickets ON tickets.ticket_no = expected.n
WHERE
  tickets.ticket_no IS NULL
ORDER BY
  expected.n;

Expected result

missing_ticket_no
3
5

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE tickets (ticket_no INT);

INSERT INTO
  tickets (ticket_no)
VALUES
  (1),
  (2),
  (4),
  (6);

Validated query

SQL

WITH
  expected (n) AS (
    SELECT
      1
    UNION ALL
    SELECT
      n + 1
    FROM
      expected
    WHERE
      n < 6
  )
SELECT
  expected.n AS missing_ticket_no
FROM
  expected
  LEFT JOIN tickets ON tickets.ticket_no = expected.n
WHERE
  tickets.ticket_no IS NULL
ORDER BY
  expected.n;

Expected result

missing_ticket_no
3
5

SQL Server omits the `RECURSIVE` keyword. The generated sequence and final anti-join are otherwise the same.

Useful when

Where this command helps.

auditing imported invoice, ticket, or batch numbers for missing values
checking a small expected id range without relying on an existing numbers table

Explanation

What the command is doing.

Missing sequence values matter when importing numbered records, auditing ticket ranges, or checking generated identifiers. A reliable pattern is to build the expected sequence, left join it to the real table, and keep the generated numbers that have no matching row. Recursive CTE syntax differs slightly in SQL Server, but the idea is the same.

Find Gaps In An Integer Sequence

Find missing ticket numbers from 1 through 6

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.