Sequence gaps — missing IDs, uncovered date ranges, holes in attendance records — hide data quality problems that standard queries never surface. This expert-level lesson teaches you every production-ready technique for finding and filling them, from self-joins to window functions, with full performance analysis.
You're auditing an order management system and someone asks you to pull all orders from last quarter. You write the query, skim the results, and something feels off — the order IDs jump from 10,423 to 10,431. Eight orders are missing. Were they deleted? Never created? Migrated incorrectly from a legacy system? You don't know yet, but you need to find out. That's the moment gap detection becomes critical.
Sequence gaps show up everywhere in real data work. Auto-increment IDs that skip when transactions roll back. Date ranges in scheduling tables that leave certain days uncovered. Invoice numbers that should be contiguous but aren't. Employee ID sequences with holes from terminated contractors. Every one of these gaps might represent a data quality issue, a business process failure, or worse — deleted records that someone doesn't want you to find. SQL gives you powerful tools to surface them, and by the end of this lesson you'll be able to use both self-joins and subqueries to find gaps systematically, reconstruct what's missing, and fill them when appropriate.
What you'll learn:
NOT EXISTS and NOT IN as alternative gap-detection strategiesYou should be comfortable with SQL JOINs — especially LEFT JOINs and self-joins — before working through this material. You'll also need a solid understanding of subqueries and their role in filtering, and ideally some exposure to advanced subquery and CTE patterns. We'll build on all three throughout the lesson.
Before writing any SQL, you need to understand what you're actually looking for. There are two fundamentally different types of gaps, and they require different techniques.
Integer sequence gaps are holes in a numeric series. Your orders table has IDs 1 through 10,000, but some are missing. You want a list of which IDs don't exist. The challenge here is that the "missing" values don't exist anywhere in your database — you're looking for absence.
Date range gaps are intervals of time where no row covers a given period. You have a product_pricing table where each row has a valid_from and valid_to date, and you need to know if any day is left uncovered. Here the data exists, but the coverage has holes.
These problems look similar on the surface but require different reasoning. Integer gaps are about comparing a sequence to itself. Date range gaps are about comparing intervals to each other. We'll tackle them separately.
Key insight
Gap detection is fundamentally a problem of finding what isn't there. That makes it structurally different from most SQL problems, which filter and transform rows that do exist. This is why LEFT JOINs, NOT EXISTS, and NOT IN — all tools for identifying absence — are central to gap detection.
The self-join approach to gap detection is elegant once you understand the logic. The idea: if you join a table to itself, pairing each row with the row that should come immediately after it, then any row where that "next" row is missing indicates a gap.
Let's work with a concrete scenario. You manage an e-commerce platform and the orders table looks like this:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
status VARCHAR(20)
);
You want to find all missing order_id values between 1 and the maximum ID. The self-join approach starts by joining orders to itself where the right side has an ID exactly one greater than the left side:
SELECT
o1.order_id AS gap_starts_after
FROM orders o1
LEFT JOIN orders o2
ON o2.order_id = o1.order_id + 1
WHERE o2.order_id IS NULL
AND o1.order_id < (SELECT MAX(order_id) FROM orders)
ORDER BY o1.order_id;
Walk through the logic: for every row in o1, we try to find a matching row in o2 where o2.order_id = o1.order_id + 1. If that join produces a NULL on the right side, it means no row exists with that next ID — so a gap starts right after o1.order_id. The final condition excludes the maximum ID itself, because there's no "next row" to expect after the last one.
This query tells you where gaps start, but it doesn't tell you how large they are or what the missing values actually are. Let's upgrade it to show you the gap range:
SELECT
o1.order_id + 1 AS gap_start,
MIN(o2.order_id) - 1 AS gap_end,
MIN(o2.order_id) - o1.order_id - 1 AS gap_size
FROM orders o1
JOIN orders o2
ON o2.order_id > o1.order_id
WHERE NOT EXISTS (
SELECT 1 FROM orders o3
WHERE o3.order_id = o1.order_id + 1
)
GROUP BY o1.order_id
ORDER BY o1.order_id;
This version uses a correlated subquery in the WHERE clause to confirm the gap exists, then groups by the starting point of each gap and finds the next actual ID (MIN(o2.order_id)) to determine where the gap ends. The result gives you something genuinely useful:
gap_start | gap_end | gap_size
----------+---------+---------
10424 | 10430 | 7
15001 | 15001 | 1
22100 | 22215 | 116
Warning
The self-join approach with a non-equijoin (o2.order_id > o1.order_id) can be very expensive on large tables. For every row in o1, it potentially scans many rows in o2. On a table with 10 million rows, this creates a query that may take minutes. Always check your execution plan before running this against production. We'll cover more scalable alternatives later.
The self-join approach naturally finds gaps between existing values, but it won't catch a gap at the very start of your sequence. If your IDs are supposed to start at 1 but the first real order has ID 5, those four missing records won't appear.
To handle this, you need to either add a sentinel row or handle it separately:
-- Check if sequence starts at expected minimum
SELECT
CASE
WHEN MIN(order_id) > 1
THEN 'Gap at start: IDs 1 through ' || (MIN(order_id) - 1)::TEXT || ' are missing'
ELSE 'Sequence starts correctly at 1'
END AS start_gap_check
FROM orders;
The self-join approach is clever but has a fundamental limitation — it only knows about the IDs that exist. For complete gap coverage, you need to compare your actual data against a complete list of what should be there. This is the reference sequence approach.
Most databases have a way to generate a series of integers. Here's how to do it in the major dialects:
PostgreSQL:
SELECT generate_series(1, 10000) AS expected_id;
MySQL (using a recursive CTE):
WITH RECURSIVE seq AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM seq WHERE n < 10000
)
SELECT n AS expected_id FROM seq;
SQL Server:
WITH seq AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM seq WHERE n < 10000
)
SELECT n AS expected_id FROM seq
OPTION (MAXRECURSION 10000);
Once you have a reference sequence, the gap-finding query becomes a straightforward LEFT JOIN — a technique you've likely used before, just applied to absence detection. If you want a refresher on how the different join types affect which rows appear in results, see Choosing the Right JOIN Type.
WITH expected AS (
SELECT generate_series(
(SELECT MIN(order_id) FROM orders),
(SELECT MAX(order_id) FROM orders)
) AS expected_id
)
SELECT e.expected_id AS missing_order_id
FROM expected e
LEFT JOIN orders o ON o.order_id = e.expected_id
WHERE o.order_id IS NULL
ORDER BY e.expected_id;
This approach is clean, readable, and complete. It will catch gaps at the start, end, and middle of your range. The downside is that generating a large series inline can be memory-intensive, and recursive CTEs in MySQL and SQL Server have default recursion limits you'll need to override for long sequences.
Tip
Rather than hard-coding the range bounds, parameterize them dynamically using MIN() and MAX() subqueries as shown above. This makes the query reusable across different tables without modification.
NOT EXISTS gives you another angle on the same problem. Instead of generating a complete sequence and finding what's missing via LEFT JOIN, you check each potential ID against the table directly. This is particularly effective when you want to frame the logic as: "for each number in this range, does a corresponding row exist?"
Mastering SQL EXISTS and NOT EXISTS covers the mechanics in depth, but the key insight here is that NOT EXISTS short-circuits — as soon as it finds a match, it stops looking. This can make it faster than LEFT JOIN in certain situations.
WITH expected AS (
SELECT generate_series(
(SELECT MIN(order_id) FROM orders),
(SELECT MAX(order_id) FROM orders)
) AS expected_id
)
SELECT expected_id AS missing_order_id
FROM expected
WHERE NOT EXISTS (
SELECT 1 FROM orders o
WHERE o.order_id = expected.expected_id
)
ORDER BY expected_id;
The result is identical to the LEFT JOIN version. The choice between them is mostly stylistic and performance-driven. On PostgreSQL with a B-tree index on order_id, both will use index seeks for the inner lookup, making performance roughly equivalent. On databases without good index usage for correlated subqueries, LEFT JOIN often performs better.
Note
NOT IN is a tempting alternative to NOT EXISTS, but it has a dangerous behavior with NULLs. If the subquery returns any NULL value, NOT IN returns no rows at all — silently, with no error. Always prefer NOT EXISTS when checking for absence. If you're unfamiliar with this NULL behavior, review NULL Handling in SQL before using NOT IN in production queries.
Integer gaps are a warm-up. Date range gaps are where things get genuinely interesting — and genuinely tricky.
Imagine you're working with a product_pricing table for a subscription business:
CREATE TABLE product_pricing (
pricing_id INT PRIMARY KEY,
product_id INT,
price DECIMAL(10, 2),
valid_from DATE NOT NULL,
valid_to DATE NOT NULL
);
Each row says "this product costs $X between these dates." For billing to work correctly, every single day must be covered by exactly one pricing row. Your task is to find any days that fall between price records — days where the price is undefined.
This is harder than integer gap detection for two reasons:
The classic approach uses a subquery to find, for each pricing row, what the next valid_from date is for the same product. If there's a gap between valid_to of row N and valid_from of row N+1, that gap is uncovered:
SELECT
p1.product_id,
p1.valid_to + INTERVAL '1 day' AS gap_start,
next_period.next_from - INTERVAL '1 day' AS gap_end,
next_period.next_from - p1.valid_to - 1 AS gap_days
FROM product_pricing p1
CROSS JOIN LATERAL (
SELECT MIN(p2.valid_from) AS next_from
FROM product_pricing p2
WHERE p2.product_id = p1.product_id
AND p2.valid_from > p1.valid_to
) AS next_period
WHERE next_period.next_from IS NOT NULL
AND next_period.next_from > p1.valid_to + INTERVAL '1 day'
ORDER BY p1.product_id, p1.valid_to;
The LATERAL subquery (supported in PostgreSQL and modern SQL Server via CROSS APPLY) evaluates once per row of p1, finding the soonest valid_from date in the same product's history that comes after the current row's valid_to. If that next start date is more than one day after the current end date, there's a gap.
If your database doesn't support LATERAL, you can rewrite this as a correlated subquery in the SELECT clause:
SELECT
p1.product_id,
p1.valid_to + INTERVAL '1 day' AS gap_start,
(
SELECT MIN(p2.valid_from)
FROM product_pricing p2
WHERE p2.product_id = p1.product_id
AND p2.valid_from > p1.valid_to
) - INTERVAL '1 day' AS gap_end
FROM product_pricing p1
WHERE (
SELECT MIN(p2.valid_from)
FROM product_pricing p2
WHERE p2.product_id = p1.product_id
AND p2.valid_from > p1.valid_to
) > p1.valid_to + INTERVAL '1 day'
ORDER BY p1.product_id, p1.valid_to;
This works but executes the correlated subquery twice — once in SELECT and once in WHERE. You can avoid duplication by wrapping it in a CTE or derived table:
WITH pricing_with_next AS (
SELECT
p1.product_id,
p1.valid_from,
p1.valid_to,
(
SELECT MIN(p2.valid_from)
FROM product_pricing p2
WHERE p2.product_id = p1.product_id
AND p2.valid_from > p1.valid_to
) AS next_valid_from
FROM product_pricing p1
)
SELECT
product_id,
valid_to + INTERVAL '1 day' AS gap_start,
next_valid_from - INTERVAL '1 day' AS gap_end,
(next_valid_from - valid_to - 1) AS gap_days
FROM pricing_with_next
WHERE next_valid_from IS NOT NULL
AND next_valid_from > valid_to + INTERVAL '1 day'
ORDER BY product_id, valid_to;
This version is cleaner and the CTE will typically be computed once, making it more efficient than the double-subquery version.
Key insight
The date arithmetic here is database-specific. PostgreSQL lets you subtract dates directly to get an integer number of days. SQL Server uses DATEDIFF. MySQL doesn't support INTERVAL syntax the same way. Always test date gap queries in a sandbox before running them in production — off-by-one errors in date boundary logic are silent and insidious. The Master SQL String and Date Functions lesson covers cross-dialect date arithmetic in detail.
While you're looking for gaps, you should also check for overlaps — periods where two pricing rows cover the same day. An overlap is as dangerous as a gap for billing logic.
SELECT
p1.pricing_id AS row1_id,
p2.pricing_id AS row2_id,
p1.product_id,
GREATEST(p1.valid_from, p2.valid_from) AS overlap_start,
LEAST(p1.valid_to, p2.valid_to) AS overlap_end
FROM product_pricing p1
JOIN product_pricing p2
ON p1.product_id = p2.product_id
AND p1.pricing_id < p2.pricing_id -- avoid self-match and duplicate pairs
AND p1.valid_from <= p2.valid_to
AND p1.valid_to >= p2.valid_from
ORDER BY p1.product_id, overlap_start;
This is a self-join that checks whether two date ranges overlap using the standard overlap condition: range A overlaps range B if A starts before B ends AND A ends after B starts. The condition p1.pricing_id < p2.pricing_id ensures you don't match a row to itself and you don't see the same overlap reported twice.
Finding gaps is step one. Step two is often deciding what to do about them. In some cases — like missing audit log entries — a gap is just a finding you report. In others — like a pricing table with uncovered dates — you need to fill the gap with actual data.
Warning
Filling gaps in production data should always involve a business decision, not just a technical one. An INSERT that fills a pricing gap with a placeholder price could affect billing calculations if other code starts using that row before you're ready. Always fill gaps in a staging environment first, and always use a transaction that you can roll back.
Suppose your business rule is that missing order IDs should be documented with a "void" record so the sequence is complete for audit purposes. Here's how you'd insert those records:
INSERT INTO orders (order_id, customer_id, order_date, status)
WITH expected AS (
SELECT generate_series(
(SELECT MIN(order_id) FROM orders),
(SELECT MAX(order_id) FROM orders)
) AS expected_id
)
SELECT
e.expected_id,
NULL AS customer_id,
NULL AS order_date,
'VOID' AS status
FROM expected e
LEFT JOIN orders o ON o.order_id = e.expected_id
WHERE o.order_id IS NULL;
This uses the same gap-detection logic inside an INSERT statement. The CTE finds all expected IDs, the LEFT JOIN filters to only those that don't exist, and the outer INSERT creates void records for each gap.
If you need a refresher on safe data modification patterns, INSERT, UPDATE, DELETE: Master SQL Data Modification Safely covers how to use transactions and staging tables to make DML reversible.
The date range case is more nuanced. When a pricing gap exists, you can't just insert a NULL price — you need a business rule. Common options: carry forward the previous price, use a default price, or flag the gap for manual review and insert a sentinel record.
Here's an approach that carries forward the last known price into the gap:
INSERT INTO product_pricing (product_id, price, valid_from, valid_to)
WITH pricing_with_next AS (
SELECT
p1.product_id,
p1.price,
p1.valid_from,
p1.valid_to,
(
SELECT MIN(p2.valid_from)
FROM product_pricing p2
WHERE p2.product_id = p1.product_id
AND p2.valid_from > p1.valid_to
) AS next_valid_from
FROM product_pricing p1
),
gaps AS (
SELECT
product_id,
price, -- carry forward the previous price
valid_to + INTERVAL '1 day' AS gap_valid_from,
next_valid_from - INTERVAL '1 day' AS gap_valid_to
FROM pricing_with_next
WHERE next_valid_from IS NOT NULL
AND next_valid_from > valid_to + INTERVAL '1 day'
)
SELECT product_id, price, gap_valid_from, gap_valid_to
FROM gaps;
This query layers two CTEs: pricing_with_next finds each row and the date when the next pricing period starts, and gaps extracts the uncovered interval and inherits the previous row's price. The INSERT then materializes those gap records.
Tip
Always run the SELECT portion of a gap-fill INSERT before actually executing the INSERT. Inspect the proposed new rows carefully. Does the valid_to of the gap row ever exceed the valid_from of the next legitimate row? If your date arithmetic is off by a day, you'll create overlaps while trying to fix gaps. Double-check the boundary values in the output.
If your database supports window functions — and any modern database does — you have a faster and more readable approach available. LAG() and LEAD() let you look at adjacent row values without a self-join.
This approach is covered extensively in CTE and window function lessons, but it's worth including here because it's the production-grade solution for large tables:
WITH ordered_pricing AS (
SELECT
product_id,
price,
valid_from,
valid_to,
LEAD(valid_from) OVER (
PARTITION BY product_id
ORDER BY valid_from
) AS next_valid_from
FROM product_pricing
)
SELECT
product_id,
valid_to + INTERVAL '1 day' AS gap_start,
next_valid_from - INTERVAL '1 day' AS gap_end,
(next_valid_from - valid_to - 1) AS gap_days
FROM ordered_pricing
WHERE next_valid_from IS NOT NULL
AND next_valid_from > valid_to + INTERVAL '1 day'
ORDER BY product_id, valid_to;
LEAD(valid_from) OVER (PARTITION BY product_id ORDER BY valid_from) looks ahead to the next row's valid_from date, within each product's history, ordered by date. The CTE computes this once; the outer query then filters to only the rows where a gap exists.
Compare this to the correlated subquery version. The window function approach:
product_pricing once (or twice with a materialized CTE)The correlated subquery version:
product_pricing(product_id, valid_from) to accelerate each inner lookupAt small scale (under ~100K rows), both are fine. At millions of rows, window functions win. Always check the execution plan with EXPLAIN ANALYZE in PostgreSQL or SET STATISTICS TIME ON in SQL Server.
Key insight
Window functions don't change what you're doing — finding the next row in a sequence — they change how the database executes it. A correlated subquery says "for each row, go find the answer separately." A window function says "compute this for all rows in one pass." That difference can be a 10x or 100x performance difference on large tables.
Gap detection queries have a reputation for being slow, but that's usually because they're written without considering the access patterns they create. Let's think through the indexing requirements for each approach.
For the self-join approach on orders:
-- The join condition is: o2.order_id = o1.order_id + 1
-- The subquery condition is: o3.order_id = o1.order_id + 1
-- Both need efficient point lookups on order_id
-- A PRIMARY KEY on order_id covers this entirely
If order_id is already your primary key (and it almost certainly is), the self-join approach uses index seeks for every row comparison. This is actually efficient for small to medium gaps detection.
For the reference sequence approach:
-- The LEFT JOIN condition is: o.order_id = e.expected_id
-- Same story: primary key index covers it
-- The generate_series result is in memory, not indexed
-- Query becomes: for each generated ID, seek into orders
-- Performance: O(n log m) where n = sequence length, m = orders count
For tables with millions of rows and sequences in the tens of millions, this can still be slow because n (the sequence length) drives the number of seeks.
The product_pricing query joins on product_id and filters/sorts by valid_from:
-- Create a composite index on (product_id, valid_from)
CREATE INDEX idx_pricing_product_dates
ON product_pricing (product_id, valid_from, valid_to);
Including valid_to as a third column makes this a covering index for the correlated subquery version, so no heap access is needed. The index supports:
WHERE p2.product_id = p1.product_id (equality on first column)AND p2.valid_from > p1.valid_to (range scan on second column)valid_from and valid_to from the index directly (covering)Warning
Adding a composite index on (product_id, valid_from) helps the gap detection query enormously, but it also adds overhead to INSERT and UPDATE operations on product_pricing. If this table receives thousands of writes per second, benchmark the impact before adding the index. For more on balancing read and write performance, see SQL Indexes Explained: How They Work and When to Create Them.
If you're detecting gaps in a table with tens of millions of rows, running a single gap-detection query against the whole table is usually the wrong approach. Instead:
MAX(id) - MIN(id) - COUNT(*) + 1 to see if gaps exist at all before running a full scan-- Quick check: are there ANY gaps in this table?
SELECT
MAX(order_id) - MIN(order_id) + 1 AS expected_count,
COUNT(*) AS actual_count,
(MAX(order_id) - MIN(order_id) + 1) - COUNT(*) AS gap_count
FROM orders;
If gap_count is 0, you're done. If it's nonzero, you know the scale of the problem and can run the full detection query with appropriate expectations.
This pattern also helps you prioritize — a gap_count of 3 might be investigated manually; a gap_count of 50,000 requires an automated pipeline.
Real-world sequences are often composite. Your monthly_report table might have gaps in a combination of (year, month) pairs. Your employee_attendance table might have gaps in (employee_id, date). Single-column techniques don't translate directly.
Here's a date-based composite gap check for an attendance table where every employee should have a record for every working day:
CREATE TABLE employee_attendance (
employee_id INT,
work_date DATE,
hours_worked DECIMAL(4,2),
PRIMARY KEY (employee_id, work_date)
);
To find missing attendance records, you need a cross product of employees × expected dates:
WITH date_spine AS (
SELECT generate_series(
'2024-01-01'::DATE,
'2024-12-31'::DATE,
'1 day'::INTERVAL
)::DATE AS expected_date
),
working_days AS (
-- Filter to weekdays only
SELECT expected_date
FROM date_spine
WHERE EXTRACT(DOW FROM expected_date) NOT IN (0, 6) -- 0=Sunday, 6=Saturday
),
all_employees AS (
SELECT DISTINCT employee_id FROM employee_attendance
),
expected_attendance AS (
SELECT e.employee_id, w.expected_date
FROM all_employees e
CROSS JOIN working_days w
)
SELECT
ea.employee_id,
ea.expected_date AS missing_date
FROM expected_attendance ea
LEFT JOIN employee_attendance att
ON att.employee_id = ea.employee_id
AND att.work_date = ea.expected_date
WHERE att.work_date IS NULL
ORDER BY ea.employee_id, ea.expected_date;
This generates the full expected set using a CROSS JOIN between employees and working days, then LEFT JOINs to actual data to surface what's missing. For 500 employees and 250 working days, the expected set has 125,000 rows — manageable in memory. For 50,000 employees, you'd want to run this per department or date range batch.
Note
The generate_series function with an INTERVAL argument returns TIMESTAMP, not DATE, in PostgreSQL. The ::DATE cast at the end of the series generation is required or date comparisons will fail silently with type mismatch coercions. This is a common source of bugs in date spine queries.
Work through this scenario to practice the full gap detection workflow. Use PostgreSQL syntax — adjust as needed for your database.
Scenario: You're a data engineer at a logistics company. The shipment_tracking table records status updates for packages, and you've been told there may be missing entries. The table schema:
CREATE TABLE shipment_tracking (
tracking_event_id INT PRIMARY KEY,
shipment_id INT,
event_timestamp TIMESTAMP,
event_status VARCHAR(50)
);
-- Sample data with intentional gaps
INSERT INTO shipment_tracking VALUES
(1, 101, '2024-01-15 09:00:00', 'PICKED_UP'),
(2, 101, '2024-01-15 14:30:00', 'IN_TRANSIT'),
(4, 102, '2024-01-16 08:00:00', 'PICKED_UP'), -- note: ID 3 is missing
(5, 102, '2024-01-16 17:00:00', 'IN_TRANSIT'),
(6, 103, '2024-01-17 11:00:00', 'PICKED_UP'),
(9, 103, '2024-01-17 23:00:00', 'DELIVERED'), -- note: IDs 7 and 8 are missing
(10, 104, '2024-01-18 09:30:00', 'PICKED_UP');
Exercise tasks:
Task 1: Write a self-join query that identifies which tracking_event_id values have a gap immediately after them. Your output should include the ID where the gap starts and the ID where the next real record appears.
Task 2: Rewrite Task 1 using the reference sequence approach with generate_series. List every individual missing ID.
Task 3: Compare the outputs of Task 1 and Task 2. Can you think of a scenario where the self-join approach would miss a gap that the reference sequence approach catches? (Hint: think about boundaries.)
Task 4: Write an INSERT statement that creates void placeholder records for all the missing IDs. Use NULL for shipment_id and event_timestamp, and 'VOID' for event_status. Wrap it in a transaction and verify the results before committing.
Task 5 (Challenge): The operations team also wants to know if any shipment_id has a gap of more than 2 hours between consecutive tracking events. Write a query that finds those shipment_id values and shows the start and end of each long gap.
Expected output for Task 2:
missing_tracking_event_id
--------------------------
3
7
8
When you write the basic self-join (LEFT JOIN ON o2.order_id = o1.order_id + 1), every row where the next ID doesn't exist gets flagged — including the maximum ID, because there's legitimately no row after the last one.
Fix: Add AND o1.order_id < (SELECT MAX(order_id) FROM orders) to exclude the final row.
If your pricing table uses inclusive valid_to dates (a row valid through December 31 has valid_to = '2024-12-31'), then a new row starting January 1 is not a gap — it's adjacent. But if you write:
WHERE next_valid_from > valid_to -- WRONG: this flags adjacent ranges as gaps
You should write:
WHERE next_valid_from > valid_to + INTERVAL '1 day' -- CORRECT: adjacent is not a gap
Warning
This boundary logic varies depending on whether your date ranges are inclusive/exclusive on each end. Document your convention and enforce it with a CHECK constraint if possible. Mixed conventions in the same table are a source of very difficult bugs.
-- DANGEROUS: if generate_series returns any NULLs, this returns no rows
SELECT expected_id
FROM generate_series(1, 1000) AS expected_id
WHERE expected_id NOT IN (SELECT order_id FROM orders);
generate_series won't return NULLs in this case, but if you replace it with a subquery against a real table where order_id might be NULL, NOT IN silently returns nothing. Prefer NOT EXISTS for robustness.
Gap detection on a non-indexed column will cause a full table scan for every row in the outer query. This can turn a sub-second query on a small table into a multi-minute query on a large one.
Diagnosis: Run EXPLAIN before running the query. Look for "Seq Scan" on the inner table — that's the warning sign. If you see it, add an index or reconsider your approach.
Not every gap is a data quality problem. Auto-increment sequences skip IDs when transactions roll back — this is expected behavior, not a bug. Deleted records legitimately leave holes. Before filling gaps, always understand why they exist.
Tip
When you find gaps, add a step to your investigation: check whether the database has a sequence object (PostgreSQL SEQUENCE, SQL Server IDENTITY) whose current value explains the skip. A sequence that was reset, incremented manually, or used for multiple purposes can explain many apparent gaps without any data loss.
If your orders table has IDs from multiple sources (domestic orders, imported from a legacy system, bulk-loaded from a partner), there's no single expected sequence. A gap between ID 10000 and ID 20001 might simply be where the domestic sequence ends and the partner-sourced sequence begins.
Fix: Always understand the business rules governing your ID column before interpreting gaps. If possible, partition your gap analysis by source system using a separate source_system column.
Gap detection is one of those SQL skills that looks simple until you actually need it in production — and then the edge cases multiply fast. Let's recap what you've learned.
You now know three distinct approaches to integer gap detection:
For date range gaps, you've learned the lead-row pattern using correlated subqueries and CTEs, as well as the window function alternative that scales better on large tables. You also know how to detect the inverse problem — overlapping ranges — using a self-join with the standard overlap condition.
You've seen how to translate gap detection into gap filling with INSERT statements, with appropriate warnings about the business decisions that need to precede any data modification.
Where to go from here:
If this lesson sparked an interest in the broader category of comparing rows to each other — not just for gap detection but for change detection, duplicate finding, and time-series analysis — dive into Writing SQL Self-Joins: Query the Same Table Twice to Compare Rows and Find Relationships. The techniques overlap significantly.
For building complete analytical queries that chain together the kinds of CTEs and subqueries you've seen here, Writing Multi-Step Analytical Queries walks through structuring complex SQL from requirements to production-ready code.
And if you want to understand why some of these queries are fast and others are slow at a deeper level, SQL Indexes Explained will give you the mental model you need to write gap detection queries that stay fast as your tables grow.