AdvancedSubqueries & EXISTS

Subquery to find gaps in sequential IDs

The query

SQL

SELECT
  a.order_id + 1 AS gap_start,
  b.order_id - 1 AS gap_end,
  (b.order_id - a.order_id - 1) AS missing_count
FROM orders a
JOIN orders b ON a.order_id + 1 < b.order_id
WHERE NOT EXISTS (
  SELECT 1 FROM orders c
  WHERE c.order_id BETWEEN a.order_id + 1 AND b.order_id - 1
)
ORDER BY gap_start;

Tested against PostgreSQL 16

Note

“Gap detection using self-join + NOT EXISTS. Classic SQL puzzle and real-world data quality check.

Tables referenced

orders6 cols