Writing SQL to Detect and Handle Data Gaps: Using LEFT JOIN and Subqueries to Find Missing Records Across Related Tables

Introduction

Picture this: your analytics team reports that monthly revenue is down 12% in March, but when you dig into the orders table, the numbers look fine. The real problem is subtler — a batch ETL job failed silently, your order_items table is missing two days of records, and because no one wrote a query to detect the gaps, the dashboard just showed lower numbers without any alarm. Data gaps aren't always missing rows in a single table. More often, they live in the relationships between tables: customers who never placed an order, products that weren't included in any monthly report, employees who have no performance review on file, or subscription plans with no billing event for a given period.

Finding these gaps is one of the most practically valuable skills in SQL, and it's also one of the most frequently misunderstood. The instinct most developers have is to reach for a WHERE filter or a COUNT() — but those approaches only work when you already know what's missing. What you need is a way to force the database to show you what should be there versus what is there, even when the "should" lives in a completely separate table or has to be generated from a reference set.

By the end of this lesson, you'll be able to systematically detect missing records across related tables using LEFT JOIN, correlated subqueries, NOT EXISTS, NOT IN, and set operations. You'll understand when each approach performs best, how each one fails under specific data conditions, and how to build production-quality gap detection queries that scale to millions of rows.

What you'll learn:

• How LEFT JOIN with a NULL filter works as a gap detection engine, and why it's not always the right choice
• How to use subqueries (NOT IN, NOT EXISTS) to find missing relationships, and how they differ in behavior and performance
• How to generate reference sets (date ranges, expected categories) and join against them to find structural gaps
• How to detect gaps in time-series data where the reference set doesn't exist as a table
• Advanced patterns: correlated subqueries for detecting per-group gaps, CTEs for readable multi-step gap analysis, and FULL OUTER JOIN for two-sided gap detection
• How to handle NULL pitfalls that silently corrupt gap detection logic

---

Prerequisites

You should be comfortable with:

• Writing SELECT, FROM, WHERE queries — if you need a refresher, see SQL Basics: Master SELECT, FROM, WHERE Clauses and Build Your First Queries
• Understanding how JOIN types work — SQL JOINs Explained with Real-World Examples covers the foundation
• Basic subquery syntax — Understanding SQL Subqueries: Filtering and Looking Up Data with Nested SELECT Statements will get you there
• NULL behavior in SQL — knowing why NULL = NULL evaluates to UNKNOWN is critical here; NULL Handling in SQL: IS NULL, COALESCE, and NULLIF explains this clearly

---

The Core Concept: Why Missing Data Is Hard to See

The reason data gaps are so hard to catch is that SQL's default behavior is inclusion-only. An INNER JOIN only shows you rows that matched on both sides. A WHERE clause only shows you rows that satisfy a condition on data that exists. Neither mechanism has a way to say "show me what's absent."

Think about a simple schema: a customers table and an orders table. If you run:

SELECT c.customer_id, COUNT(o.order_id) AS order_count
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

This query silently drops every customer who has never placed an order. You'd have no idea those customers existed unless you thought to check customers against orders separately. In a business context, those zero-order customers might be the most important segment to understand — they're churned customers, or newly acquired customers who haven't converted, or test accounts that slipped into production.

The reference set is sometimes a table in your database (customers, products, employees), sometimes a derived set you generate (every date in a date range, every combination of region × product category), and sometimes a set defined by business logic (every employee hired before 2023 should have a 2023 review). Identifying which type of reference set you're dealing with shapes which technique you'll use.

---

Method 1: LEFT JOIN with IS NULL — The Workhorse Pattern

The most common and often most readable approach is the anti-join pattern: LEFT JOIN combined with a WHERE IS NULL filter on the right table. Here's the logic:

A LEFT JOIN returns all rows from the left table, plus matched rows from the right table. When there's no match, the right-side columns are filled with NULL. If you then filter for rows where a right-side column IS NULL, you're left with only the rows from the left table that had no match in the right table.

Basic example: Customers with no orders

SELECT 
    c.customer_id,
    c.email,
    c.signup_date,
    c.acquisition_channel
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

This returns every customer who has never placed an order. The LEFT JOIN ensures all customers appear; the WHERE o.order_id IS NULL filters to only those with no matching order.

Scoped gaps: Customers with no orders in a specific period

Real data gap problems are almost always scoped. You don't just want customers who never ordered — you want customers who were active in Q1 2024 but placed no order in Q2 2024:

SELECT 
    c.customer_id,
    c.email,
    c.signup_date
FROM customers c
INNER JOIN orders q1 
    ON c.customer_id = q1.customer_id
    AND q1.order_date >= '2024-01-01'
    AND q1.order_date < '2024-04-01'
LEFT JOIN orders q2 
    ON c.customer_id = q2.customer_id
    AND q2.order_date >= '2024-04-01'
    AND q2.order_date < '2024-07-01'
WHERE q2.order_id IS NULL;

Notice the structure here. The INNER JOIN to q1 restricts the result to customers who did order in Q1. The LEFT JOIN to q2 (with a date filter in the ON clause, not the WHERE clause) checks for Q2 orders. The WHERE q2.order_id IS NULL finds customers who had Q1 activity but no Q2 activity.

Multi-table gap detection: Products with no inventory record

Sometimes the gap is across three tables. Say you have products, warehouses, and inventory (which maps products to warehouses with a quantity). You want to find every combination of product and warehouse that has no inventory record at all — perhaps because the inventory sync job failed for certain products:

SELECT 
    p.product_id,
    p.product_name,
    w.warehouse_id,
    w.warehouse_name
FROM products p
CROSS JOIN warehouses w
LEFT JOIN inventory i 
    ON i.product_id = p.product_id
    AND i.warehouse_id = w.warehouse_id
WHERE i.inventory_id IS NULL
    AND p.is_active = TRUE
    AND w.is_active = TRUE;

The CROSS JOIN generates every possible product-warehouse combination (the reference set). The LEFT JOIN to inventory checks which combinations actually have records. The IS NULL filter reveals the gaps. The filters on is_active narrow the reference set to combinations that should reasonably exist.

This pattern — CROSS JOIN to build a reference set, then LEFT JOIN to check for presence — is incredibly powerful for detecting structural gaps in many-to-many relationships.

---

Method 2: NOT IN — Clean Syntax, Hidden Danger

NOT IN with a subquery is syntactically elegant and easy to read:

SELECT 
    product_id,
    product_name
FROM products
WHERE product_id NOT IN (
    SELECT DISTINCT product_id 
    FROM monthly_sales_summary
    WHERE report_month = '2024-03-01'
);

This asks: which products have no record in the March sales summary? The intent is crystal clear. But NOT IN has a behavior that will burn you if you're not aware of it.

The NULL problem with NOT IN

If the subquery returns even a single NULL value, the entire NOT IN check returns UNKNOWN for every row, and you get zero results. This is because NOT IN is evaluated as a series of <> comparisons, and any comparison to NULL yields UNKNOWN, which SQL treats as falsy.

-- This looks safe, but if product_id is nullable in monthly_sales_summary,
-- this query might return zero rows even when gaps exist
SELECT product_id
FROM products
WHERE product_id NOT IN (
    SELECT product_id FROM monthly_sales_summary
    WHERE report_month = '2024-03-01'
);

The fix is to filter NULLs out of the subquery explicitly:

SELECT product_id
FROM products
WHERE product_id NOT IN (
    SELECT product_id 
    FROM monthly_sales_summary
    WHERE report_month = '2024-03-01'
    AND product_id IS NOT NULL  -- defensive NULL filter
);

Performance considerations for NOT IN

On large tables, NOT IN with a subquery can perform poorly because many databases materialize the subquery into a list and then check each row from the outer query against that list. For tables with millions of rows, this becomes expensive. The query planner on modern databases (PostgreSQL, SQL Server, MySQL 8+) often rewrites NOT IN to a hash anti-join internally, but you shouldn't rely on this. When performance matters, prefer NOT EXISTS or the LEFT JOIN / IS NULL pattern, which give the optimizer more flexibility.

---

Method 3: NOT EXISTS — The Correlated Approach

NOT EXISTS is the most flexible and generally safest approach for gap detection. Unlike NOT IN, it's NULL-safe. Unlike LEFT JOIN, it can express more complex matching conditions cleanly.

SELECT 
    e.employee_id,
    e.full_name,
    e.department,
    e.hire_date
FROM employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM performance_reviews pr
    WHERE pr.employee_id = e.employee_id
        AND pr.review_year = 2023
);

This finds every employee who has no 2023 performance review. The subquery is correlated — it references e.employee_id from the outer query. For each employee row, SQL checks whether any matching review exists. If none does, NOT EXISTS returns TRUE and the employee appears in the results.

Correlated NOT EXISTS for per-group gaps

The real power of NOT EXISTS is in detecting gaps that vary per group. Suppose you need to find every sales representative who is missing a monthly check-in record for any month in the past quarter:

-- First, let's construct what we need:
-- For each (rep, month) combination in Q1 2024, 
-- is there a check-in record?

SELECT 
    r.rep_id,
    r.rep_name,
    m.month_start
FROM sales_reps r
CROSS JOIN (
    VALUES 
        ('2024-01-01'::date),
        ('2024-02-01'::date),
        ('2024-03-01'::date)
) AS m(month_start)
WHERE NOT EXISTS (
    SELECT 1
    FROM rep_checkins rc
    WHERE rc.rep_id = r.rep_id
        AND rc.checkin_date >= m.month_start
        AND rc.checkin_date < m.month_start + INTERVAL '1 month'
)
AND r.is_active = TRUE;

This is sophisticated. The CROSS JOIN against a VALUES list generates the reference set: every (rep, month) pair we expect. The correlated NOT EXISTS subquery checks whether any check-in exists for that rep in that month's date range. The result is a precise list of which reps are missing check-ins for which months.

This pattern — CROSS JOIN to generate the reference set, NOT EXISTS to check presence — is one of the cleanest ways to express multi-dimensional gap detection.

---

Method 4: Generating Reference Sets for Time-Series Gap Detection

Date-based gaps are among the most common in data engineering. If you're tracking daily sales, daily active users, or any time-series metric, you need a complete spine of dates to detect gaps properly.

Using a calendar table

Many production databases maintain a dedicated calendar or date-dimension table. If yours does, gap detection is straightforward:

SELECT 
    cal.calendar_date,
    COALESCE(SUM(o.revenue), 0) AS daily_revenue,
    COUNT(o.order_id) AS order_count
FROM calendar_dates cal
LEFT JOIN orders o 
    ON o.order_date = cal.calendar_date
    AND o.status = 'completed'
WHERE cal.calendar_date BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY cal.calendar_date
ORDER BY cal.calendar_date;

Any date with no orders shows up with revenue of 0 and order_count of 0, rather than being invisible. This is the most robust approach for time-series reporting — the reference set is always explicitly present.

Generating a date range inline

If you don't have a calendar table, you can generate one. PostgreSQL makes this easy with generate_series:

WITH date_spine AS (
    SELECT generate_series(
        '2024-01-01'::date,
        '2024-03-31'::date,
        '1 day'::interval
    )::date AS report_date
)
SELECT 
    ds.report_date,
    COUNT(o.order_id) AS order_count,
    COALESCE(SUM(o.revenue), 0) AS daily_revenue
FROM date_spine ds
LEFT JOIN orders o 
    ON o.order_date = ds.report_date
    AND o.status = 'completed'
GROUP BY ds.report_date
ORDER BY ds.report_date;

In SQL Server, you'd use a recursive CTE to generate the series:

WITH date_spine AS (
    SELECT CAST('2024-01-01' AS DATE) AS report_date
    UNION ALL
    SELECT DATEADD(DAY, 1, report_date)
    FROM date_spine
    WHERE report_date < '2024-03-31'
)
SELECT 
    ds.report_date,
    COUNT(o.order_id) AS order_count,
    COALESCE(SUM(o.revenue), 0.00) AS daily_revenue
FROM date_spine ds
LEFT JOIN orders o 
    ON o.order_date = ds.report_date
    AND o.status = 'completed'
GROUP BY ds.report_date
ORDER BY ds.report_date
OPTION (MAXRECURSION 366);

In MySQL, without generate_series or easy recursive CTEs for older versions, you'd typically join against a numbers table or use a stored procedure to populate a temporary table. MySQL 8+ supports recursive CTEs:

WITH RECURSIVE date_spine AS (
    SELECT DATE('2024-01-01') AS report_date
    UNION ALL
    SELECT DATE_ADD(report_date, INTERVAL 1 DAY)
    FROM date_spine
    WHERE report_date < '2024-03-31'
)
SELECT 
    ds.report_date,
    COUNT(o.order_id) AS order_count,
    COALESCE(SUM(o.revenue), 0) AS daily_revenue
FROM date_spine ds
LEFT JOIN orders o 
    ON o.order_date = ds.report_date
    AND o.status = 'completed'
GROUP BY ds.report_date
ORDER BY ds.report_date;

For more complex multi-step analytical structures like this, Advanced Subqueries and CTEs: Mastering Complex SQL Query Architecture goes deep on how to chain these patterns effectively.

Detecting gaps in sequential data

Sometimes you're not checking against a calendar but against a sequence of IDs or events that should be contiguous. If your order IDs are supposed to be sequential integers and you want to find gaps:

WITH order_sequence AS (
    SELECT 
        order_id,
        LEAD(order_id) OVER (ORDER BY order_id) AS next_order_id
    FROM orders
)
SELECT 
    order_id AS gap_starts_after,
    next_order_id AS gap_ends_before,
    next_order_id - order_id - 1 AS missing_count
FROM order_sequence
WHERE next_order_id - order_id > 1;

This uses LEAD() to look at the next row's ID. If the difference is greater than 1, there are missing IDs in that range. This is useful for auditing whether records were deleted or whether an auto-increment sequence was corrupted.

---

Method 5: FULL OUTER JOIN for Two-Sided Gap Detection

Sometimes you need to detect gaps from both directions simultaneously — which rows exist in table A but not B, and which exist in B but not A. This is classic data reconciliation work, such as comparing an external vendor feed to your internal records.

SELECT 
    COALESCE(internal.product_sku, vendor.product_sku) AS product_sku,
    internal.product_name AS internal_name,
    vendor.product_name AS vendor_name,
    CASE 
        WHEN internal.product_sku IS NULL THEN 'In vendor feed, missing internally'
        WHEN vendor.product_sku IS NULL THEN 'In internal catalog, missing from vendor'
        ELSE 'Matched'
    END AS reconciliation_status,
    internal.price AS internal_price,
    vendor.price AS vendor_price,
    internal.price - vendor.price AS price_discrepancy
FROM internal_product_catalog internal
FULL OUTER JOIN vendor_product_feed vendor
    ON internal.product_sku = vendor.product_sku
WHERE internal.product_sku IS NULL 
    OR vendor.product_sku IS NULL
    OR ABS(internal.price - vendor.price) > 0.01;

The FULL OUTER JOIN preserves all rows from both sides. The WHERE clause then filters to only the interesting cases: unmatched rows from either side, or rows where the price disagrees. The COALESCE(internal.product_sku, vendor.product_sku) in the SELECT gives you the SKU regardless of which side it came from.

This pattern — Choosing the Right JOIN Type: When to Use INNER, LEFT, and FULL OUTER JOIN for Clean Aggregation Results explores all the tradeoffs in detail — is critical for any kind of data pipeline validation or external data reconciliation workflow.

---

Method 6: Set Operations for Clean Gap Detection

SQL's set operations offer another readable approach to gap detection. Combining Results with SQL Set Operations: UNION, UNION ALL, INTERSECT, and EXCEPT covers these in depth, but for gap detection, EXCEPT (or MINUS in Oracle) is the key operator.

EXCEPT returns all rows from the first query that don't appear in the second query:

-- Products that should have been included in the March report
SELECT product_id FROM products WHERE is_active = TRUE
EXCEPT
SELECT product_id FROM monthly_sales_summary WHERE report_month = '2024-03-01';

This is elegant and readable. But it has limitations: it works on complete row equality across all columns in the select list. Both queries must have the same number of columns with compatible types. And it can be slower than LEFT JOIN or NOT EXISTS for large datasets because some databases handle set operations less efficiently than join-based anti-joins.

EXCEPT really shines when you're comparing two snapshots of the same table structure — for example, finding IDs that were in yesterday's data export but aren't in today's:

SELECT order_id FROM orders_snapshot_2024_03_14
EXCEPT
SELECT order_id FROM orders_snapshot_2024_03_15;

This immediately reveals any orders that were present yesterday but deleted or modified (and deleted from the snapshot) today.

---

Building a Comprehensive Gap Detection Query with CTEs

In production work, gap detection rarely happens in a single simple query. You're usually combining multiple reference sets, scoping by multiple conditions, and producing output that can be acted upon by downstream processes. CTEs make this readable and maintainable.

Let's walk through a realistic end-to-end example: detecting which account managers are missing required monthly activity logs for Q1 2024, broken down by activity type.

-- Define our reference sets and then find gaps
WITH 
-- All active account managers
active_managers AS (
    SELECT 
        employee_id,
        full_name,
        region,
        manager_tier
    FROM employees
    WHERE role = 'account_manager'
        AND employment_status = 'active'
        AND hire_date < '2024-01-01'  -- Only those employed all of Q1
),

-- The months we care about
q1_months AS (
    SELECT month_start
    FROM (VALUES 
        ('2024-01-01'::date),
        ('2024-02-01'::date),
        ('2024-03-01'::date)
    ) AS t(month_start)
),

-- Required activity types per tier
required_activities AS (
    SELECT 
        manager_tier,
        activity_type
    FROM (VALUES
        ('senior', 'client_call'),
        ('senior', 'pipeline_review'),
        ('senior', 'forecast_submission'),
        ('junior', 'client_call'),
        ('junior', 'forecast_submission')
    ) AS t(manager_tier, activity_type)
),

-- All (manager, month, required_activity) combinations that should exist
expected_activity_combinations AS (
    SELECT 
        am.employee_id,
        am.full_name,
        am.region,
        am.manager_tier,
        qm.month_start,
        ra.activity_type
    FROM active_managers am
    CROSS JOIN q1_months qm
    INNER JOIN required_activities ra ON ra.manager_tier = am.manager_tier
),

-- What actually exists in the activity log
logged_activities AS (
    SELECT 
        employee_id,
        activity_type,
        DATE_TRUNC('month', activity_date)::date AS activity_month
    FROM manager_activity_log
    WHERE activity_date >= '2024-01-01'
        AND activity_date < '2024-04-01'
        AND is_valid = TRUE
)

-- Find gaps: expected combinations with no corresponding log entry
SELECT 
    eac.employee_id,
    eac.full_name,
    eac.region,
    eac.manager_tier,
    TO_CHAR(eac.month_start, 'YYYY-MM') AS missing_month,
    eac.activity_type AS missing_activity_type
FROM expected_activity_combinations eac
LEFT JOIN logged_activities la
    ON la.employee_id = eac.employee_id
    AND la.activity_type = eac.activity_type
    AND la.activity_month = eac.month_start
WHERE la.employee_id IS NULL
ORDER BY eac.region, eac.full_name, eac.month_start, eac.activity_type;

Walk through what this does:

1. active_managers — defines the reference set of people who should have records
2. q1_months — defines the time scope
3. required_activities — encodes business rules about what each tier must log
4. expected_activity_combinations — generates every (person, month, activity) triplet that must exist
5. logged_activities — fetches and normalizes what's actually there
6. The final SELECT does the anti-join to find the gaps

This is the pattern for production gap detection: explicit reference sets, explicit actual data, clean anti-join. The CTEs make each step readable and independently testable.

---

Performance Considerations and Index Strategy

Gap detection queries can be expensive because they're anti-joins — they require the database to scan at least one side fully and check for absence. Understanding how to optimize them is critical for production use.

Index coverage for the anti-join

For LEFT JOIN / IS NULL and NOT EXISTS patterns, the key is having indexes that support the join condition. If you're detecting which customers have no orders:

-- The join is: o.customer_id = c.customer_id
-- You need an index on orders(customer_id)
-- The optimizer will scan customers and do an index lookup per customer into orders

If orders.customer_id has an index, the database can quickly determine for each customer whether any orders exist. Without that index, the database may resort to a hash anti-join or nested loop over a full scan, which can be extremely slow for large tables.

NOT EXISTS vs LEFT JOIN: optimizer behavior

Most modern query optimizers treat NOT EXISTS and LEFT JOIN / IS NULL identically — they both generate an anti-join plan. However, there are subtle exceptions:

• In PostgreSQL, both patterns reliably generate the same hash anti-join or merge anti-join plan.
• In SQL Server, NOT EXISTS sometimes produces a slightly better plan when the subquery is complex, because the optimizer can apply semi-join elimination more aggressively.
• In MySQL (pre-8.0), NOT EXISTS was historically better optimized than NOT IN. In MySQL 8+, the optimizer handles all three patterns similarly.

When in doubt, check EXPLAIN or EXPLAIN ANALYZE (in PostgreSQL) / EXPLAIN FORMAT=JSON (in MySQL) / the execution plan viewer in SQL Server to see which plan the optimizer chose.

Scaling considerations for large reference sets

When your reference set is generated (a date spine, a CROSS JOIN of products and warehouses), the intermediate set can be very large. A year of daily dates times 10,000 products = 3.65 million rows in the reference set before you even apply the anti-join. For these cases:

1. Filter early — apply all available filters to reduce the reference set before the CROSS JOIN, not after
2. Consider materialized temp tables — for reference sets used multiple times in a complex report, write the reference set to a temp table with appropriate indexes before joining
3. Partition the problem — if you're checking gaps for 50,000 customers over 365 days, consider processing in date-range batches if the full cross product is unmanageable

For deep coverage of index strategy that applies here, SQL Indexes Explained: How They Work and When to Create Them is essential reading.

---

Handling the Results: Quantifying and Prioritizing Gaps

Detecting gaps is only half the job. In practice, you need to quantify them, prioritize them, and often route them to different downstream processes.

Summarizing gap counts per dimension

WITH gap_analysis AS (
    SELECT 
        p.product_id,
        p.category,
        p.product_name,
        m.report_month
    FROM products p
    CROSS JOIN (
        SELECT DISTINCT DATE_TRUNC('month', order_date)::date AS report_month
        FROM orders
        WHERE order_date >= '2023-01-01'
    ) m
    WHERE p.launch_date <= m.report_month
        AND (p.discontinue_date IS NULL OR p.discontinue_date > m.report_month)
    AND NOT EXISTS (
        SELECT 1
        FROM monthly_product_summary mps
        WHERE mps.product_id = p.product_id
            AND mps.report_month = m.report_month
    )
)
SELECT 
    category,
    COUNT(DISTINCT product_id) AS products_with_gaps,
    COUNT(*) AS total_gap_months,
    MIN(report_month) AS earliest_gap,
    MAX(report_month) AS latest_gap
FROM gap_analysis
GROUP BY category
ORDER BY total_gap_months DESC;

This turns the raw gap list into a prioritized summary by category, showing you where the data quality problems are concentrated. The MIN and MAX of report_month tell you whether gaps are isolated or span a long period.

Combining gap detection with [CASE WHEN](/articles/writing-sql-case-expressions-conditional-logic-inside-select-where-and-group-by) for severity classification

SELECT 
    c.customer_id,
    c.customer_segment,
    c.annual_contract_value,
    DATEDIFF('day', MAX(o.order_date), CURRENT_DATE) AS days_since_last_order,
    CASE
        WHEN MAX(o.order_date) IS NULL THEN 'never_ordered'
        WHEN DATEDIFF('day', MAX(o.order_date), CURRENT_DATE) > 365 THEN 'lapsed_over_1yr'
        WHEN DATEDIFF('day', MAX(o.order_date), CURRENT_DATE) > 180 THEN 'lapsed_6mo'
        WHEN DATEDIFF('day', MAX(o.order_date), CURRENT_DATE) > 90 THEN 'at_risk'
        ELSE 'active'
    END AS engagement_status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_segment, c.annual_contract_value
HAVING MAX(o.order_date) IS NULL 
    OR DATEDIFF('day', MAX(o.order_date), CURRENT_DATE) > 90
ORDER BY c.annual_contract_value DESC;

This query uses a LEFT JOIN and aggregation together to both detect the gap (no recent orders) and classify its severity, then orders by contract value to prioritize the highest-revenue accounts at risk. For more on filtering groups this way, see Filtering Groups After Aggregation: Writing HAVING Clauses That Answer Real Business Questions.

---

Hands-On Exercise

Work through these progressively. Each builds on the last.

Setup

Assume these tables in a SaaS subscription context:

-- accounts: companies using the platform
CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    company_name VARCHAR(200),
    plan_tier VARCHAR(50),  -- 'starter', 'growth', 'enterprise'
    signup_date DATE,
    churn_date DATE  -- NULL if still active
);

-- billing_events: monthly billing records
CREATE TABLE billing_events (
    billing_id INT PRIMARY KEY,
    account_id INT,
    billing_month DATE,  -- first day of month (e.g., 2024-01-01)
    amount_charged DECIMAL(10,2),
    payment_status VARCHAR(20),  -- 'paid', 'failed', 'pending'
    created_at TIMESTAMP
);

-- feature_usage: monthly feature engagement logs
CREATE TABLE feature_usage (
    usage_id INT PRIMARY KEY,
    account_id INT,
    feature_name VARCHAR(100),
    usage_month DATE,
    event_count INT
);

Exercise 1: Basic gap detection

Find all accounts that signed up before 2024 but have no billing event in January 2024. Include the account name, plan tier, and signup date. Exclude churned accounts (where churn_date is not NULL and is before January 2024).

Exercise 2: Scoped multi-period gap

Find accounts that had a paid billing event in Q4 2023 (October, November, December — billing_month between '2023-10-01' and '2023-12-01' inclusive) but are missing at least one billing event in Q1 2024. Return each missing month as a separate row.

Hint: Use CROSS JOIN with a VALUES list for the Q1 months, then a NOT EXISTS subquery.

Exercise 3: Two-sided reconciliation

Find accounts where the number of months with billing events in 2023 does not match the number of months with any feature_usage records in 2023. Return the account_id, billing month count, feature usage month count, and the difference. Order by the absolute discrepancy descending.

Exercise 4: Generate and join a date spine

Using generate_series (PostgreSQL) or a recursive CTE (SQL Server/MySQL), produce a report of daily billing event counts for January 2024, including days with zero events. Ensure every day from Jan 1–31 appears in the result even if no billing events occurred on that date.

Exercise 5: Advanced — Tiered gap detection

Enterprise-tier accounts are required to have at least 3 distinct features logged in feature_usage each month they're active. Find all enterprise accounts and months where this requirement wasn't met. Define "active" as: account signed up before that month and either not churned, or churned during or after that month.

---

Common Mistakes & Troubleshooting

Mistake 1: Putting right-table filters in the WHERE clause instead of ON

Symptom: Your gap detection query returns zero rows, or far fewer than expected.

Cause: Moving a condition on the right (potentially-NULL) table to WHERE turns the LEFT JOIN into an effective INNER JOIN.

-- BROKEN: date filter in WHERE eliminates NULL rows
SELECT c.customer_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01'  -- This kills the NULLs
AND o.order_id IS NULL;  -- Now nothing matches

-- CORRECT: date filter in ON clause
SELECT c.customer_id
FROM customers c
LEFT JOIN orders o 
    ON c.customer_id = o.customer_id
    AND o.order_date >= '2024-01-01'
WHERE o.order_id IS NULL;

Mistake 2: NOT IN returning zero results due to NULLs

Symptom: NOT IN query returns no rows even when gaps clearly exist.

Cause: The subquery is returning at least one NULL value.

Fix: Add AND key_column IS NOT NULL to the subquery, or switch to NOT EXISTS.

Mistake 3: Forgetting to scope the reference set

Symptom: You get thousands of gaps that don't make business sense — for example, products flagged as missing from a report when they were discontinued years ago.

Cause: The reference set is too broad. You included all products, not just active ones during the relevant period.

Fix: Always apply lifecycle/status filters to the reference set, not just the fact table. WHERE p.is_active = TRUE AND p.launch_date <= m.report_month etc.

Mistake 4: CROSS JOIN explosion

Symptom: The query takes forever or runs out of memory.

Cause: A CROSS JOIN between two large tables produced a reference set with billions of rows.

Fix: Filter both sides aggressively before the CROSS JOIN. Consider whether you truly need all combinations or whether you can scope the problem. Use EXPLAIN to see the estimated row counts before running.

Mistake 5: Confusing "no row" with "row with NULL value"

Symptom: An account appears in your gap report even though you can see a billing record for them in the billing table.

Cause: The billing record exists, but the amount or status column you're checking has a NULL value, and your join condition or filter is matching on a non-key column that happens to be NULL.

Fix: Always join on primary keys or unique identifiers. Use separate conditions for data quality checks (like AND payment_status = 'paid') rather than baking them into join conditions that could cause confusion. For deep coverage of NULL behavior, revisit NULL Handling in SQL: IS NULL, COALESCE, and NULLIF.

Mistake 6: Not handling timezone-aware timestamps for date-based gaps

Symptom: Your daily gap report shows certain dates as having zero events even though you know events occurred.

Cause: Your event timestamps are stored in UTC, but you're comparing to local dates. An event at 11 PM Eastern US time is stored as 4 AM UTC the next day — wrong date when you truncate without converting.

Fix: Always AT TIME ZONE 'your_local_timezone' before truncating to date, or store dates in a separate column in local time if your business logic is timezone-sensitive.

---

Summary & Next Steps

Gap detection is fundamentally about explicit reference sets and anti-joins. The technique you choose — LEFT JOIN / IS NULL, NOT IN, NOT EXISTS, EXCEPT, or FULL OUTER JOIN — depends on the shape of the problem, the data's NULL behavior, and the performance requirements of your environment.

Here's a quick decision guide:

• Reference set is a table, single join condition → LEFT JOIN / IS NULL (fast, readable)
• Reference set is a subquery, complex correlated conditions → NOT EXISTS (flexible, NULL-safe)
• Reference set is a subquery, simple non-nullable key → NOT IN (readable, but validate for NULLs)
• Comparing two datasets of identical structure → EXCEPT (elegant for reconciliation)
• Need gaps from both sides simultaneously → FULL OUTER JOIN
• Time-series or multi-dimensional gaps → Generate a reference spine with CROSS JOIN + VALUES/generate_series/recursive CTE, then LEFT JOIN or NOT EXISTS

The deeper skill here is learning to think in terms of "what should exist" vs. "what does exist" — a mental model that applies beyond SQL to data quality testing, pipeline validation, and operational monitoring generally.

To extend these skills further:

• Mastering SQL EXISTS and NOT EXISTS: Correlated Subquery Patterns for Filtering with Related Data covers the full depth of correlated subquery patterns
• Advanced Subqueries and CTEs: Mastering Complex SQL Query Architecture will help you structure complex multi-step gap analysis queries
• For combining gap detection with aggregate reporting in a single pass, Multi-Table Reporting with JOIN and GROUP BY: Aggregating Across Relationships in a Single Query bridges both skills

Data gaps are a fundamental data quality problem, and writing confident, correct queries to detect them separates engineers who trust their reports from those who just hope they're right.