Writing SQL to Detect Missing Foreign Key References: Using LEFT JOIN and NOT EXISTS to Audit Referential Integrity Without Constraints

Introduction

You're three weeks into a data migration project. The source system had no enforced foreign keys — it was a legacy application where the development team "trusted the application layer" to maintain data integrity. Now you're trying to load this data into a new schema, and rows are failing left and right. Orders referencing customer IDs that don't exist. Line items pointing to products that were deleted years ago. Shipments linked to warehouse locations that never made it into the reference table. The application worked fine for a decade because nobody directly queried the database, but the moment you try to treat the data relationally, the holes appear everywhere.

This is the scenario that motivates everything in this lesson. Even in systems that do use foreign key constraints, you'll encounter situations where constraints were disabled during bulk loads, dropped during migrations, or simply never existed in NoSQL stores, data warehouses, or denormalized reporting databases. Being able to audit referential integrity with pure SQL — without relying on the database engine to enforce it for you — is a production-critical skill.

By the end of this lesson, you'll be able to write queries that systematically detect orphaned records, quantify the scope of integrity violations, identify patterns in the missing references, and produce audit reports that actually help you decide what to do about the problem. These techniques work across PostgreSQL, MySQL, SQL Server, BigQuery, Snowflake, and virtually any SQL dialect.

What you'll learn:

• How referential integrity works conceptually, and why the absence of constraints doesn't mean data is clean
• How to use LEFT JOIN with a NULL check to find records with missing foreign key references
• How NOT EXISTS with a correlated subquery accomplishes the same goal via a fundamentally different execution path, and when each approach is preferable
• How to build multi-table integrity audits that check relationships across an entire schema
• How to handle NULLs in foreign key columns without producing false positives
• Performance considerations and query optimization strategies for large-scale integrity audits

Prerequisites

This lesson assumes you're comfortable writing multi-table queries. You should understand how SQL JOINs work and have a solid grasp of subquery patterns. If you're fuzzy on how LEFT JOIN differs from INNER JOIN, review choosing the right JOIN type before continuing. Familiarity with NULL handling is particularly important — it's central to both techniques we'll cover.

---

Understanding Referential Integrity Without Constraints

Before writing a single query, it helps to be precise about what you're looking for. A foreign key relationship expresses a rule: every value in column A of table X must have a matching value in column B of table Y. When a database engine enforces this rule, it rejects any INSERT or UPDATE that would violate it. When there's no enforcement, the rule becomes a claim — something the data is supposed to satisfy but which needs to be verified empirically.

The concept of an orphaned record is central here. An orphaned record is a row in a child table whose foreign key value does not match any primary key (or unique key) in the parent table. The row exists, it has a value in the FK column, but that value doesn't correspond to anything real in the reference table.

There are several distinct categories you'll encounter in practice:

Category 1: Hard orphans. The foreign key column contains a value that simply doesn't exist in the parent table. An order has customer_id = 9847, but customer 9847 was never inserted, was deleted, or was truncated during a migration. These are the most straightforward violations.

Category 2: NULL foreign keys. The foreign key column contains NULL. Depending on your business rules, this may be valid (optional relationships) or invalid (required relationships). SQL handles these differently from hard orphans, and conflating the two categories will produce misleading counts.

Category 3: Type mismatch orphans. The foreign key column and the primary key column technically contain the same value, but stored as different types — an integer 42 vs. a string '42'. Without enforced constraints, this happens more often than you'd expect. The JOIN will silently fail to match them, and you'll see orphans that aren't really orphans.

Category 4: Case sensitivity orphans. In case-sensitive collations, 'Widget' and 'widget' don't match. In a system where product codes were entered inconsistently, you'll see apparent orphans that are actually case-mismatch problems.

Understanding these categories changes how you write your queries and how you interpret the results. Throughout this lesson, we'll address each one explicitly.

---

The Schema We'll Use

Let's establish a realistic working scenario. We're auditing the database for a mid-sized e-commerce operation that migrated data from a legacy PHP application. The database runs PostgreSQL 15, and foreign key constraints were disabled during the migration load and never re-enabled.

Here's our schema:

-- Core tables (parent tables / reference tables)
CREATE TABLE customers (
    customer_id     INTEGER PRIMARY KEY,
    email           VARCHAR(255) NOT NULL,
    country_code    CHAR(2),
    created_at      TIMESTAMP
);

CREATE TABLE products (
    product_id      INTEGER PRIMARY KEY,
    sku             VARCHAR(50) UNIQUE,
    category_id     INTEGER,
    supplier_id     INTEGER,
    is_active       BOOLEAN DEFAULT TRUE
);

CREATE TABLE categories (
    category_id     INTEGER PRIMARY KEY,
    category_name   VARCHAR(100),
    parent_category_id INTEGER  -- self-referencing
);

CREATE TABLE suppliers (
    supplier_id     INTEGER PRIMARY KEY,
    supplier_name   VARCHAR(200)
);

CREATE TABLE warehouses (
    warehouse_id    INTEGER PRIMARY KEY,
    warehouse_name  VARCHAR(100),
    country_code    CHAR(2)
);

-- Transaction tables (child tables)
CREATE TABLE orders (
    order_id        INTEGER PRIMARY KEY,
    customer_id     INTEGER,   -- should reference customers.customer_id
    placed_at       TIMESTAMP,
    status          VARCHAR(20)
);

CREATE TABLE order_items (
    item_id         INTEGER PRIMARY KEY,
    order_id        INTEGER,   -- should reference orders.order_id
    product_id      INTEGER,   -- should reference products.product_id
    quantity        INTEGER,
    unit_price      NUMERIC(10,2)
);

CREATE TABLE shipments (
    shipment_id     INTEGER PRIMARY KEY,
    order_id        INTEGER,   -- should reference orders.order_id
    warehouse_id    INTEGER,   -- should reference warehouses.warehouse_id
    shipped_at      TIMESTAMP
);

CREATE TABLE inventory (
    inventory_id    INTEGER PRIMARY KEY,
    product_id      INTEGER,   -- should reference products.product_id
    warehouse_id    INTEGER,   -- should reference warehouses.warehouse_id
    quantity_on_hand INTEGER
);

The relationships this schema should enforce:

• orders.customer_id → customers.customer_id
• order_items.order_id → orders.order_id
• order_items.product_id → products.product_id
• shipments.order_id → orders.order_id
• shipments.warehouse_id → warehouses.warehouse_id
• products.category_id → categories.category_id
• products.supplier_id → suppliers.supplier_id
• inventory.product_id → products.product_id
• inventory.warehouse_id → warehouses.warehouse_id
• categories.parent_category_id → categories.category_id (self-referencing)

---

Technique 1: LEFT JOIN with NULL Check

The LEFT JOIN approach is the most intuitive way to find missing references. The logic is elegant: a LEFT JOIN returns all rows from the left table, matched with any rows from the right table. When no match exists, the right table's columns are filled with NULL. So if you filter for rows where the right table's key column IS NULL, you've isolated exactly the rows that have no match.

The Basic Pattern

-- Find orders with no matching customer
SELECT
    o.order_id,
    o.customer_id,
    o.placed_at,
    o.status
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

Let's trace through the execution mentally:

1. For each row in orders, the database looks for a matching row in customers where customer_id values match.
2. When a match exists, the customer columns are populated and the WHERE clause finds c.customer_id IS NOT NULL — that row is excluded.
3. When no match exists, c.customer_id is NULL — that row passes the filter and appears in results.

The result is exactly the set of orders that reference customer IDs that don't exist.

Separating True Orphans from NULL Foreign Keys

Here's the first important subtlety. What if o.customer_id itself is NULL? A NULL customer_id will never match any customer (NULL ≠ NULL in SQL), so it will also produce a NULL c.customer_id after the LEFT JOIN. If you just run the basic query above, you'll count NULL foreign keys as orphans — which may or may not be what you want.

Let's separate these cases explicitly:

-- Separate NULL FK values from genuine hard orphans
SELECT
    o.order_id,
    o.customer_id,
    o.placed_at,
    o.status,
    CASE
        WHEN o.customer_id IS NULL THEN 'null_fk'
        ELSE 'orphaned'
    END AS violation_type
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL
ORDER BY violation_type, o.order_id;

This single query gives you both categories at once and labels them. For summary counts:

SELECT
    CASE
        WHEN o.customer_id IS NULL THEN 'null_fk'
        ELSE 'orphaned'
    END AS violation_type,
    COUNT(*) AS record_count
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL
GROUP BY violation_type;

You might see output like:

violation_type	record_count
null_fk	234
orphaned	1,847

That tells you something important: 234 orders never had a customer ID assigned (perhaps guest checkouts that weren't implemented consistently), while 1,847 orders reference customer IDs that genuinely don't exist in the customer table.

Extending to Multiple Relationships

Now let's audit order_items, which has two foreign keys that need checking:

-- Find order items with missing orders OR missing products
SELECT
    oi.item_id,
    oi.order_id,
    oi.product_id,
    oi.quantity,
    oi.unit_price,
    CASE WHEN o.order_id IS NULL AND oi.order_id IS NOT NULL
         THEN 'orphaned_order' END AS order_violation,
    CASE WHEN p.product_id IS NULL AND oi.product_id IS NOT NULL
         THEN 'orphaned_product' END AS product_violation
FROM order_items oi
LEFT JOIN orders   o ON oi.order_id   = o.order_id
LEFT JOIN products p ON oi.product_id = p.product_id
WHERE o.order_id IS NULL
   OR p.product_id IS NULL;

This query chains two LEFT JOINs and uses an OR condition to catch either type of violation. The CASE expressions in the SELECT let you see, for each row, which relationship is broken — because a single item might have both violations simultaneously.

---

Technique 2: NOT EXISTS with a Correlated Subquery

The NOT EXISTS approach achieves the same logical result through a fundamentally different mechanism. Instead of joining and looking for NULLs, NOT EXISTS runs a subquery for each row in the outer query and checks whether the subquery returns any rows at all.

The Basic Pattern

-- Find orders with no matching customer using NOT EXISTS
SELECT
    o.order_id,
    o.customer_id,
    o.placed_at,
    o.status
FROM orders o
WHERE NOT EXISTS (
    SELECT 1
    FROM customers c
    WHERE c.customer_id = o.customer_id
);

The correlated subquery (WHERE c.customer_id = o.customer_id) references the outer query's current row. For each order, the database checks whether any customer exists with that customer_id. If the subquery returns no rows, NOT EXISTS evaluates to TRUE, and the order appears in the result.

Notice SELECT 1 inside the subquery — the actual values returned don't matter, only whether rows exist. This is conventional practice and may help the optimizer.

How NOT EXISTS Handles NULLs Differently

Here's a critical behavioral difference from the LEFT JOIN approach. When o.customer_id is NULL, the correlated subquery becomes WHERE c.customer_id = NULL. In SQL, comparisons with NULL using = always evaluate to UNKNOWN (not TRUE, not FALSE). So the WHERE clause filters out every row in customers, the subquery returns no rows, NOT EXISTS evaluates to TRUE, and the NULL FK order appears as an "orphan."

The behavior is the same as LEFT JOIN in this regard — you still need to handle NULL FKs explicitly. But the reason is different. With NOT EXISTS, you can exclude NULL FKs like this:

SELECT
    o.order_id,
    o.customer_id,
    o.placed_at,
    o.status
FROM orders o
WHERE o.customer_id IS NOT NULL  -- exclude NULL FKs explicitly
  AND NOT EXISTS (
      SELECT 1
      FROM customers c
      WHERE c.customer_id = o.customer_id
  );

Adding o.customer_id IS NOT NULL to the outer WHERE clause before the NOT EXISTS check ensures you're only flagging genuine hard orphans. This is cleaner than the CASE expression approach with LEFT JOIN when you only want one category.

If you want to dig deeper into correlated subquery patterns, the EXISTS family of operators has rich behavior worth understanding in depth.

NOT EXISTS with Composite Keys

Some relationships are defined on composite keys — a combination of two or more columns that together form the unique identifier. NOT EXISTS handles this cleanly:

-- Hypothetical: inventory references product+warehouse combination
-- that must exist in a product_warehouse_config table
SELECT
    i.inventory_id,
    i.product_id,
    i.warehouse_id,
    i.quantity_on_hand
FROM inventory i
WHERE NOT EXISTS (
    SELECT 1
    FROM product_warehouse_config pwc
    WHERE pwc.product_id   = i.product_id
      AND pwc.warehouse_id = i.warehouse_id
);

The equivalent LEFT JOIN approach requires joining on both columns, which is straightforward, but the NOT EXISTS pattern often reads more clearly when you have multiple join conditions because the correlation logic lives inside the subquery WHERE clause, close to what it's checking.

---

LEFT JOIN vs. NOT EXISTS: Which Should You Use?

Both approaches are logically equivalent — they will return identical rows given identical data (with one exception we'll cover). The choice comes down to performance characteristics, database optimizer behavior, and readability. This is worth understanding in depth because getting it wrong on a table with 100 million rows is very expensive.

How Query Optimizers Handle Each Pattern

Modern query optimizers (PostgreSQL's, SQL Server's, MySQL's InnoDB optimizer) are generally smart enough to transform NOT EXISTS into an anti-join operation, which is essentially the same execution plan as a LEFT JOIN with a NULL check. In many cases, they produce identical execution plans.

However, there are scenarios where they diverge:

Scenario 1: Large parent table, small child table. NOT EXISTS with an indexed lookup on the parent table can be extremely efficient — the correlated subquery uses the index and short-circuits as soon as it finds a match. The database performs at most one index lookup per child row. A LEFT JOIN in this scenario may cause the optimizer to choose a hash join or merge join strategy that materializes more data.

Scenario 2: Large child table, large parent table, both indexed. Here the execution plans tend to converge. Both approaches will typically use a hash anti-join or a merge anti-join, and the performance difference is negligible.

Scenario 3: Unindexed parent table. If customers.customer_id has no index (unusual for a primary key, but common in data warehouse tables that use surrogate keys without indexes), NOT EXISTS can become pathologically slow — a full scan of customers for every row in orders. The LEFT JOIN approach may allow the optimizer to build a hash table once and probe it repeatedly, which is more efficient.

The Exception: Duplicate Keys in the Parent Table

The one case where LEFT JOIN and NOT EXISTS can produce different row counts: if the parent table has duplicate values in the referenced column (which shouldn't happen with a true primary key, but absolutely can happen in uncontrolled databases).

-- If customers has two rows with customer_id = 500 (bad data in parent table)
-- LEFT JOIN will return two matched rows for every order with customer_id = 500
-- NOT EXISTS will return zero (the first match causes EXISTS to short-circuit)

This is an edge case, but it matters during legacy audits. If you're using LEFT JOIN to check referential integrity and the parent table itself has duplicates in the reference column, your orphan detection query can silently multiply rows or behave unexpectedly. NOT EXISTS is immune to this problem because it only cares whether any match exists, not how many.

For the same reason, NOT EXISTS is often preferable when you're auditing data where you can't fully trust the parent table's uniqueness. Run a uniqueness check on the parent table's key column first, or use NOT EXISTS to be safe.

---

Building a Complete Schema Audit

Now let's build something genuinely useful: a comprehensive audit that checks all the relationships in our schema in a single, structured output. We'll use a CTE-based architecture to keep it readable.

WITH
-- Audit: orders.customer_id → customers.customer_id
customer_violations AS (
    SELECT
        'orders'                    AS child_table,
        'customers'                 AS parent_table,
        'customer_id'               AS fk_column,
        o.order_id::TEXT            AS child_pk,
        o.customer_id::TEXT         AS fk_value,
        CASE WHEN o.customer_id IS NULL THEN 'null_fk' ELSE 'orphaned' END AS violation_type
    FROM orders o
    LEFT JOIN customers c ON o.customer_id = c.customer_id
    WHERE c.customer_id IS NULL
),

-- Audit: order_items.order_id → orders.order_id
item_order_violations AS (
    SELECT
        'order_items'               AS child_table,
        'orders'                    AS parent_table,
        'order_id'                  AS fk_column,
        oi.item_id::TEXT            AS child_pk,
        oi.order_id::TEXT           AS fk_value,
        CASE WHEN oi.order_id IS NULL THEN 'null_fk' ELSE 'orphaned' END AS violation_type
    FROM order_items oi
    LEFT JOIN orders o ON oi.order_id = o.order_id
    WHERE o.order_id IS NULL
),

-- Audit: order_items.product_id → products.product_id
item_product_violations AS (
    SELECT
        'order_items'               AS child_table,
        'products'                  AS parent_table,
        'product_id'                AS fk_column,
        oi.item_id::TEXT            AS child_pk,
        oi.product_id::TEXT         AS fk_value,
        CASE WHEN oi.product_id IS NULL THEN 'null_fk' ELSE 'orphaned' END AS violation_type
    FROM order_items oi
    LEFT JOIN products p ON oi.product_id = p.product_id
    WHERE p.product_id IS NULL
),

-- Audit: shipments.warehouse_id → warehouses.warehouse_id
shipment_warehouse_violations AS (
    SELECT
        'shipments'                 AS child_table,
        'warehouses'                AS parent_table,
        'warehouse_id'              AS fk_column,
        s.shipment_id::TEXT         AS child_pk,
        s.warehouse_id::TEXT        AS fk_value,
        CASE WHEN s.warehouse_id IS NULL THEN 'null_fk' ELSE 'orphaned' END AS violation_type
    FROM shipments s
    LEFT JOIN warehouses w ON s.warehouse_id = w.warehouse_id
    WHERE w.warehouse_id IS NULL
),

-- Audit: products.category_id → categories.category_id
product_category_violations AS (
    SELECT
        'products'                  AS child_table,
        'categories'                AS parent_table,
        'category_id'               AS fk_column,
        p.product_id::TEXT          AS child_pk,
        p.category_id::TEXT         AS fk_value,
        CASE WHEN p.category_id IS NULL THEN 'null_fk' ELSE 'orphaned' END AS violation_type
    FROM products p
    LEFT JOIN categories cat ON p.category_id = cat.category_id
    WHERE cat.category_id IS NULL
),

-- Combine all violations
all_violations AS (
    SELECT * FROM customer_violations
    UNION ALL
    SELECT * FROM item_order_violations
    UNION ALL
    SELECT * FROM item_product_violations
    UNION ALL
    SELECT * FROM shipment_warehouse_violations
    UNION ALL
    SELECT * FROM product_category_violations
)

-- Summary by relationship and violation type
SELECT
    child_table,
    parent_table,
    fk_column,
    violation_type,
    COUNT(*)        AS violation_count
FROM all_violations
GROUP BY child_table, parent_table, fk_column, violation_type
ORDER BY child_table, parent_table, fk_column, violation_type;

This produces a summary table showing every relationship, broken down by violation type. If you want to see the actual offending rows instead of just counts, replace the final SELECT with:

SELECT *
FROM all_violations
ORDER BY child_table, parent_table, fk_column, child_pk;

---

Auditing Self-Referencing Relationships

The categories table has a self-referencing foreign key: parent_category_id references category_id in the same table. This is a common pattern for hierarchical data (categories, organizational charts, comment threads). Auditing it requires joining the table to itself, which looks like a self-join.

-- Find categories with invalid parent_category_id references
SELECT
    child_cat.category_id,
    child_cat.category_name,
    child_cat.parent_category_id,
    CASE
        WHEN child_cat.parent_category_id IS NULL THEN 'null_fk'
        ELSE 'orphaned'
    END AS violation_type
FROM categories child_cat
LEFT JOIN categories parent_cat
    ON child_cat.parent_category_id = parent_cat.category_id
WHERE parent_cat.category_id IS NULL
  AND child_cat.parent_category_id IS NOT NULL  -- skip top-level categories
;

The key here is that NULL parent_category_id is expected and valid for top-level categories — they have no parent. So we explicitly exclude NULL FKs with child_cat.parent_category_id IS NOT NULL and focus only on categories that claim to have a parent but that parent doesn't exist.

There's an additional subtlety with hierarchical data: circular references. Even if every parent_category_id points to a valid category_id, it's possible to have cycles (A is parent of B, B is parent of A). The LEFT JOIN approach won't catch this — it only validates that the referenced row exists, not that the relationship structure is acyclic. Detecting cycles requires recursive CTEs or application-layer graph traversal.

---

Quantifying Impact: Percentage of Violations

Finding orphans is step one. Communicating the severity to stakeholders requires context. What percentage of records are affected? Are violations concentrated in a particular time period? A particular subset of products?

-- Impact assessment for orders → customers relationship
SELECT
    total_orders,
    orphaned_orders,
    null_fk_orders,
    ROUND(100.0 * orphaned_orders / NULLIF(total_orders, 0), 2) AS orphan_pct,
    ROUND(100.0 * null_fk_orders  / NULLIF(total_orders, 0), 2) AS null_fk_pct
FROM (
    SELECT
        COUNT(*)                                                        AS total_orders,
        COUNT(*) FILTER (WHERE c.customer_id IS NULL
                           AND o.customer_id IS NOT NULL)               AS orphaned_orders,
        COUNT(*) FILTER (WHERE o.customer_id IS NULL)                   AS null_fk_orders
    FROM orders o
    LEFT JOIN customers c ON o.customer_id = c.customer_id
) AS summary;

The NULLIF(total_orders, 0) protects against division-by-zero if the table is empty. The FILTER clause (a PostgreSQL feature also supported in modern SQL Server and SQLite) allows conditional counting in a single scan — no need for separate subqueries. If your database doesn't support FILTER, you can use CASE WHEN inside aggregate functions to achieve the same thing.

Time-Based Pattern Analysis

Orphans often aren't uniformly distributed. They frequently cluster around migration events, software deployments, or specific time windows where something went wrong. Analyzing the temporal distribution can tell you a lot about root cause:

-- Are orphaned orders concentrated in a specific time window?
SELECT
    DATE_TRUNC('month', o.placed_at) AS order_month,
    COUNT(*)                          AS total_orders,
    COUNT(*) FILTER (
        WHERE c.customer_id IS NULL
          AND o.customer_id IS NOT NULL
    )                                 AS orphaned_orders,
    ROUND(
        100.0 * COUNT(*) FILTER (
            WHERE c.customer_id IS NULL
              AND o.customer_id IS NOT NULL
        ) / NULLIF(COUNT(*), 0),
    2)                                AS orphan_pct
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
GROUP BY DATE_TRUNC('month', o.placed_at)
ORDER BY order_month;

If you see orphan_pct spike to 85% for three months in 2021 and then drop back to 0.3%, you now have a very specific hypothesis: something happened to customer data in that period. That's vastly more useful than knowing "we have 1,847 orphaned orders."

This kind of aggregation across a JOIN is a powerful analytical pattern that goes beyond just finding violations.

---

Detecting Type Mismatch Orphans

We mentioned earlier that type mismatches can produce false orphans. This is particularly common when:

• Primary keys are integers in the parent table but were stored as strings in the child table (or vice versa)
• UUID columns have inconsistent formatting (550e8400-e29b-41d4-a716-446655440000 vs. 550E8400E29B41D4A716446655440000)
• Codes that should be numeric are stored with leading zeros as strings ('007' vs. 7)

Here's how to diagnose a potential type mismatch:

-- Step 1: Check the data types of both columns
SELECT
    column_name,
    table_name,
    data_type,
    character_maximum_length,
    numeric_precision
FROM information_schema.columns
WHERE (table_name = 'orders'    AND column_name = 'customer_id')
   OR (table_name = 'customers' AND column_name = 'customer_id')
ORDER BY table_name;

If the types differ, your LEFT JOIN will either:

1. Fail with a type error (strict systems like PostgreSQL)
2. Silently cast one side (MySQL, SQL Server in some configurations), potentially producing wrong results

-- Step 2: If you suspect type mismatch, try casting and compare counts
-- Query with implicit type (should produce a type error or cast warning)
SELECT COUNT(*)
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

-- Query with explicit cast on the FK column
SELECT COUNT(*)
FROM orders o
LEFT JOIN customers c ON CAST(o.customer_id AS INTEGER) = c.customer_id
WHERE c.customer_id IS NULL;

-- If the counts differ, you had a type mismatch causing false orphans

For string-based keys where case sensitivity might be an issue:

-- Case-insensitive comparison for string FK columns
SELECT
    o.order_id,
    o.customer_ref_code
FROM orders o
LEFT JOIN customers c
    ON LOWER(o.customer_ref_code) = LOWER(c.ref_code)
WHERE c.customer_id IS NULL
  AND o.customer_ref_code IS NOT NULL;

---

Using EXCEPT to Find Missing References

There's a third approach worth knowing: set operations. The EXCEPT operator (or MINUS in Oracle) returns rows in the first query that don't appear in the second query. For referential integrity checking, it's particularly useful when you want to find which specific FK values are missing, rather than which child rows are orphaned.

-- Which customer_id values appear in orders but not in customers?
SELECT DISTINCT customer_id
FROM orders
WHERE customer_id IS NOT NULL

EXCEPT

SELECT customer_id
FROM customers;

This tells you the specific "ghost" customer IDs — the values in orders.customer_id that have no corresponding customers row. This is different from counting orphaned orders: if 500 orders all reference the same non-existent customer_id, the EXCEPT query returns one row (that customer_id value), while the LEFT JOIN query returns 500 rows.

For a remediation workflow, knowing the distinct missing IDs is often what you need. You can build a lookup table of missing references and investigate each one:

-- Build a remediation target list
SELECT
    'orders' AS child_table,
    'customers' AS parent_table,
    'customer_id' AS fk_column,
    missing_id,
    COUNT(child_rows.order_id) AS affected_rows
FROM (
    SELECT DISTINCT customer_id AS missing_id
    FROM orders
    WHERE customer_id IS NOT NULL
    EXCEPT
    SELECT customer_id FROM customers
) AS missing_ids
JOIN orders child_rows ON child_rows.customer_id = missing_id
GROUP BY missing_id
ORDER BY affected_rows DESC;

This tells you each missing customer ID, ranked by how many orders are affected. The ones with the most affected rows are your highest-priority remediation targets.

---

Performance Optimization for Large-Scale Audits

When auditing tables with tens of millions of rows, the naive approach is too slow for interactive use. Here are the optimization strategies that actually matter.

Index Strategy

The JOIN column in the parent table (the PK or unique key) is almost certainly indexed — that's the nature of primary keys. The join column in the child table (the FK column) is often not indexed in systems without enforced FK constraints, because index creation on FK columns is often automatic only when the constraint is declared.

-- Check if the FK column has an index (PostgreSQL)
SELECT
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'orders'
  AND indexdef ILIKE '%customer_id%';

If the FK column is unindexed, the optimizer has to perform a full scan of the child table for every probe. For integrity audits (which typically run as batch operations, not user-facing queries), this may be acceptable. But if you're running these audits regularly, consider:

-- Create a partial index covering only non-NULL FK values
-- This minimizes index size while maximizing audit query performance
CREATE INDEX CONCURRENTLY idx_orders_customer_id
ON orders(customer_id)
WHERE customer_id IS NOT NULL;

The CONCURRENTLY option (PostgreSQL) builds the index without locking the table, which is essential on production systems.

Running Audits on Partitioned Tables

If your tables are partitioned (common for time-series data like orders), specify the partition range explicitly to avoid scanning historical partitions where you know integrity was clean:

-- Audit only the last 90 days (assuming range partitioning on placed_at)
SELECT
    o.order_id,
    o.customer_id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL
  AND o.customer_id IS NOT NULL
  AND o.placed_at >= CURRENT_DATE - INTERVAL '90 days';

Sampling for Quick Estimates

When you need a fast estimate rather than a complete count on a 500-million-row table:

-- PostgreSQL: use TABLESAMPLE for a statistical estimate
SELECT
    COUNT(*) * 100 AS estimated_orphan_count  -- scale up from 1% sample
FROM orders TABLESAMPLE BERNOULLI(1)  -- 1% sample
WHERE customer_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM customers c
      WHERE c.customer_id = orders.customer_id
  );

BERNOULLI(1) samples approximately 1% of rows randomly. Scale the count up by 100 to estimate the total. This turns a 10-minute query into a 6-second one for getting a ballpark figure before committing to a full audit.

---

Hands-On Exercise

Here's a scenario to work through. Download or set up the following minimal dataset, or adapt these patterns to a database you have access to.

Setup:

-- Create the tables
CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(100));
CREATE TABLE employees  (emp_id INT PRIMARY KEY, emp_name VARCHAR(100),
                         dept_id INT, manager_id INT, hired_date DATE);

-- Insert sample data with intentional integrity violations
INSERT INTO departments VALUES
(1, 'Engineering'), (2, 'Marketing'), (3, 'Finance');

INSERT INTO employees VALUES
(101, 'Alice Chen',    1, NULL,  '2019-03-15'),  -- No manager (VP level)
(102, 'Bob Okafor',    1, 101,   '2020-07-01'),  -- Valid
(103, 'Carol Santos',  2, 101,   '2021-02-28'),  -- Valid
(104, 'Dan Reeves',    9, 102,   '2022-05-10'),  -- dept_id=9 doesn't exist!
(105, 'Eva Lindqvist', 3, 999,   '2022-11-15'),  -- manager_id=999 doesn't exist!
(106, 'Frank Nwosu',   NULL, 102, '2023-01-20'), -- NULL dept_id
(107, 'Grace Kim',     2, 101,   '2023-06-01');  -- Valid

Your tasks:

1. Write a LEFT JOIN query that finds all employees with a missing department reference. Separate NULL dept_ids from genuine orphans.

2. Write a NOT EXISTS query that finds all employees whose manager_id doesn't match any existing emp_id (the self-referencing relationship). Exclude NULL manager_ids.

3. Combine both audits into a single CTE-based query that produces a summary showing: violation_category, violation_type (null_fk vs orphaned), count.

4. Write a query using EXCEPT to find the specific missing dept_id values referenced by employees.

5. Modify task 2 to also detect employees where the manager_id references an employee in a different department (a business rule violation, not a referential integrity violation — this requires a JOIN rather than a simple existence check).

Expected results for task 1:

• Dan Reeves: orphaned (dept_id=9)
• Frank Nwosu: null_fk (dept_id IS NULL)

Expected results for task 2:

• Eva Lindqvist: manager_id=999 doesn't exist in employees

---

Common Mistakes & Troubleshooting

Mistake 1: Joining on the Wrong Column

This sounds obvious, but it happens constantly when tables have multiple integer ID columns. Always double-check that your JOIN condition connects the actual FK column to the actual PK column, not two columns that happen to have similar names or similar-looking values.

-- Wrong: joining order_id to order_id on order_items (self-join of child table)
-- This checks nothing useful about referential integrity
LEFT JOIN orders o ON oi.item_id = o.order_id  -- BUG: item_id ≠ order_id

-- Correct:
LEFT JOIN orders o ON oi.order_id = o.order_id

Mistake 2: Filtering on a Nullable Right-Table Column

If you filter on a nullable column from the right table instead of the join key, you'll get false positives:

-- Problematic: c.email IS NULL could mean "no matching customer"
-- OR "matching customer exists but email is NULL"
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.email IS NULL;  -- WRONG for integrity checking

-- Correct: filter on the key column, which is NOT NULL when a match exists
WHERE c.customer_id IS NULL;  -- RIGHT

Mistake 3: Accidentally INNER JOINing the Parent Table

If you add any filter on the right table that runs before the NULL check, you convert your LEFT JOIN into an effective INNER JOIN:

-- This silently converts to an INNER JOIN because the WHERE clause
-- filters out the NULLs that LEFT JOIN produced for unmatched customers,
-- AND ALSO filters out matched customers where is_active = FALSE
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.is_active = TRUE;  -- This eliminates all NULL rows from LEFT JOIN!

-- Correct: use an AND condition in the JOIN clause if you need to filter
-- on the parent table, then separately handle the NULL check
FROM orders o
LEFT JOIN customers c
    ON o.customer_id = c.customer_id
    AND c.is_active = TRUE
WHERE c.customer_id IS NULL;
-- Now this finds orders where: no customer exists, OR customer is inactive

Mistake 4: Ignoring Duplicate Keys in the Parent Table

As discussed earlier, if the parent table has duplicate values in its "PK" column (which shouldn't happen with actual primary keys but is common in uncontrolled legacy data), your LEFT JOIN orphan query will produce duplicate child rows. Add a check:

-- Verify uniqueness of the parent table's key column before trusting your audit
SELECT customer_id, COUNT(*) AS occurrences
FROM customers
GROUP BY customer_id
HAVING COUNT(*) > 1;

If this returns rows, resolve the parent table's duplicates before running integrity audits on child tables.

Mistake 5: Forgetting to Test on a Representative Sample First

A comprehensive schema audit on production can take 20-40 minutes on large tables. Run it with LIMIT 100 on each CTE first to verify logic, then remove the LIMIT for the full run.

---

Summary & Next Steps

You now have a complete toolkit for auditing referential integrity with SQL. Let's consolidate what you've built:

The LEFT JOIN pattern joins the child table to the parent table and filters for rows where the parent's key column IS NULL after the join. It's intuitive, easy to extend to multiple relationships simultaneously, and naturally integrates with aggregation. Watch for: filtering on nullable right-table columns (use the key column), and accidental INNER JOIN behavior from WHERE conditions.

The NOT EXISTS pattern uses a correlated subquery to check existence. It's immune to parent-table duplicates, often reads cleanly for complex composite key checks, and can be more explicit about NULL FK handling with an outer IS NOT NULL condition. Watch for: performance on unindexed parent tables and the importance of verifying execution plans.

The EXCEPT pattern answers the complementary question: not "which child rows are orphaned" but "which FK values are missing." Essential for remediation planning.

The CTE-based audit framework lets you check an entire schema's worth of relationships in a single, readable, maintainable query. Parameterize it, schedule it, and pipe the output to a monitoring table for ongoing data quality tracking.

The underlying skill you're developing here is the ability to translate business rules into SQL predicates — referential integrity is just one category of business rule that the database may or may not be enforcing for you.

Where to go from here:

• Extend the audit framework with window functions to identify which user/process/batch introduced violations (if you have audit timestamps or modification tracking)
• Combine integrity audit results with INSERT/UPDATE/DELETE patterns to build automated remediation scripts that handle orphans systematically
• Study SQL indexes in depth to understand why FK column indexing matters and how to fix performance on large-scale audits
• Apply schema design principles to understand when and why to enforce FK constraints at the database level versus handling integrity in application code or audit queries

The queries in this lesson aren't just diagnostic tools — they're decision-making infrastructure. When you can quantify exactly how many records are affected, which FK values are missing, and when violations were introduced, you're in a position to make informed choices about remediation priority, root cause investigation, and whether to enforce constraints going forward. That's the difference between knowing you have a data quality problem and understanding it.