Find missing records with SQL anti-joins

An anti-join answers an absence question: Which rows in one population have no qualifying match in another? It is useful for finding orders without customers, invoices without payments, expected files without deliveries and customers without a completed purchase.

The phrase “qualifying match” matters. A customer may have an order but no completed order. A payment may exist but fail to cover the invoice. The match definition must capture the business question rather than merely compare two columns.

The examples use the original synthetic commerce dataset. Its deliberate exceptions make the answers inspectable: O1009 refers to missing customer C999; C003 has only cancelled/pending orders; C007 has no orders.

Find an order with no customer record

The NOT EXISTS pattern describes the question directly:

SELECT o.order_id, o.customer_id
FROM orders AS o
WHERE NOT EXISTS (
    SELECT 1
    FROM customers AS c
    WHERE c.customer_id = o.customer_id
)
ORDER BY o.order_id;

Expected result: O1009 and C999. For each order, the subquery asks whether any matching customer exists. The outer query retains the order when the answer is no.

SELECT 1 does not mean that one customer is counted or returned. For EXISTS, the relevant property is whether the subquery yields a row. The actual projected value is not the business result.

You can express the same absence check with a LEFT JOIN:

SELECT o.order_id, o.customer_id
FROM orders AS o
LEFT JOIN customers AS c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL
ORDER BY o.order_id;

This preserves orders first, then selects those whose right-side customer key is missing. Test a right-side column that is guaranteed non-NULL when a match exists. Testing c.email IS NULL would be wrong here because legitimate customer records can have missing email addresses.

Find customers with no completed orders

Now the left population changes. We want known customers for whom no completed order exists:

SELECT c.customer_id
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.status = 'completed'
)
ORDER BY c.customer_id;

The expected result is C003 and C007. C003 appears because its orders do not meet the status condition. C007 appears because it has no orders at all.

If you remove the status condition, only C007 remains. That is not a performance optimization of the first query; it changes the question from “no completed orders” to “no orders.”

This distinction is useful when defining customer activation. A signup with an abandoned checkout is not necessarily an activated customer, and an order's existence does not establish payment or delivery.

Understand the NOT IN and NULL trap

NOT IN can be appropriate, but a NULL in the candidate set can produce surprising results. Consider this self-contained example:

WITH expected_ids(id) AS (
    VALUES ('A'), ('B')
), received_ids(id) AS (
    VALUES ('A'), (NULL)
)
SELECT id
FROM expected_ids
WHERE id NOT IN (SELECT id FROM received_ids);

In SQLite, this returns no rows. For B, the set comparison includes an unknown value, so the predicate is not established as true. The WHERE clause does not retain that row.

A correlated NOT EXISTS version returns B:

WITH expected_ids(id) AS (
    VALUES ('A'), ('B')
), received_ids(id) AS (
    VALUES ('A'), (NULL)
)
SELECT e.id
FROM expected_ids AS e
WHERE NOT EXISTS (
    SELECT 1 FROM received_ids AS r WHERE r.id = e.id
);

This does not make NULL identity matching magically correct. If the left key can also be NULL, decide whether that represents an invalid key, an unknown entity or an intended match category. Usually a separate invalid-key report is clearer than treating missing identifiers as ordinary entities.

For the documented semantics of EXISTS and IN/NOT IN, consult the SQLite expression reference. Check dialect behaviour before translating a query to another engine.

Treat absence as a signal to investigate

An unmatched record is not automatically a mistake. A dimension may arrive later than the transaction. A customer may be intentionally anonymized. An invoice may still be within its payment window.

Attach enough context to the exception report to distinguish these cases: the source key, event timestamp, expected arrival window and current status. A daily pipeline should avoid escalating a normal ten-minute delay as a permanent data defect.

For large datasets, inspect the execution plan and indexing of the join keys. Do not assume one spelling is always faster; optimizers, statistics and data distributions matter. First confirm that two candidate queries express the same semantics.

Practice: define “customers without a completed January order” and implement it with NOT EXISTS. Place the January boundaries inside the subquery because they define a qualifying order. Explain what happens to a customer whose only completed purchase occurred in February.

These checks fit naturally into the SQL and pipeline work in NeuraPath's Data Analytics with Generative AI course. They are also useful verification tasks for an AI-generated report: ask what records are missing, why they are missing, and whether the exclusion was intended.

Continue learning

This article is part of the SQL foundations for reliable analysis sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in INNER JOIN versus LEFT JOIN with unmatched customers.
• Continue with One-to-many joins: reconcile revenue after a join.