INNER JOIN versus LEFT JOIN with unmatched customers

An INNER JOIN returns matching combinations from both inputs. A LEFT JOIN also preserves rows from its left input when no right-side match exists, filling the missing right-side values with NULL.

The important reporting question is therefore: Should an eligible order disappear because its customer details are missing? The answer depends on the metric contract. For the completed-order-value metric in this lesson, the answer is no. The order remains eligible, and the missing customer becomes a quality exception.

We will use the synthetic commerce lab. It contains eight completed orders worth 104,000 paise. Order O1009 is worth 9,000 paise and references customer C999, which is deliberately absent from the customer table.

Reproduce the lost order

SELECT
    COUNT(*) AS completed_orders,
    SUM(o.order_total_paise) AS completed_value_paise
FROM orders AS o
INNER JOIN customers AS c ON c.customer_id = o.customer_id
WHERE o.status = 'completed';

The result is seven orders and 95,000 paise. There is no SQL error. The join did exactly what was requested: it retained only orders with a matching customer record.

The analytical error is assuming that the resulting population still represents all completed orders. The missing 9,000 paise can make a regional dashboard disagree with an order-level control report.

A common response is to assume the control report is stale or the dashboard needs a refresh. Checking unmatched keys is often a faster first step.

Preserve the eligible population

SELECT
    COUNT(*) AS completed_orders,
    SUM(o.order_total_paise) AS completed_value_paise
FROM orders AS o
LEFT JOIN customers AS c ON c.customer_id = o.customer_id
WHERE o.status = 'completed';

Now the result remains eight orders and 104,000 paise. The customer primary key is unique in this fixture, so the join cannot multiply an order through multiple customer matches. In a real dataset, verify that uniqueness; LEFT JOIN prevents unmatched-row loss but does not prevent fan-out.

The distinction between matching and preservation is part of SQL's join semantics, described in the PostgreSQL table-expression documentation. This lab executes the corresponding queries in SQLite.

Show the unresolved dimension explicitly

If you group by region, the unmatched order needs a visible category:

SELECT
    CASE
        WHEN c.customer_id IS NULL THEN 'Unmatched customer'
        WHEN c.region IS NULL THEN 'Missing region'
        ELSE c.region
    END AS region_status,
    COUNT(*) AS completed_orders,
    SUM(o.order_total_paise) AS value_paise
FROM orders AS o
LEFT JOIN customers AS c ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
GROUP BY
    CASE
        WHEN c.customer_id IS NULL THEN 'Unmatched customer'
        WHEN c.region IS NULL THEN 'Missing region'
        ELSE c.region
    END
ORDER BY region_status;

Expected values are North 22,000, South 37,000, West 36,000 and Unmatched customer 9,000 paise. Together they reconcile to 104,000.

Two exception labels are used intentionally. A missing customer record and a present customer with an unknown region are different problems, likely owned by different source processes. Collapsing both into “Other” may make the chart simpler but the remediation less clear.

A WHERE condition can undo preservation

Consider a LEFT JOIN followed by WHERE c.region = 'North'. Orders with no customer match have NULL region values, so they do not pass that condition. That may be correct if the question is specifically about known North-region orders. It is incorrect if the report still claims to include all completed orders.

Similarly, if you begin with customers and want their completed-order counts including zeros, put the order eligibility condition in the ON clause. Filtering the right-side order status in WHERE removes customers with no qualifying order. The COUNT lesson shows that pattern with expected results.

Neither placement is universally better. They answer different questions because one controls matching and the other filters the joined result.

Keep an exception report beside the aggregate

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

Expected exception: O1009, C999, 9,000 paise. This small output gives the source-system owner something actionable. “The dashboard is short by ₹90” is less useful than identifying the missing join key and affected record.

A production process might quarantine unresolved orders rather than include them in a published metric. That is a valid policy if documented. In that case, publish the eligible, included and quarantined totals separately so the exclusion remains visible.

Decide from the population, then test

Use INNER JOIN when the intersection is the intended population. Use LEFT JOIN when the left population must survive incomplete enrichment. In either case, validate key uniqueness, unmatched rows and control totals.

Exercise: remove a known customer's dimension row in a copy of the lab. Predict how the INNER JOIN total changes, then verify that the LEFT JOIN total stays constant while the exception total increases. Restore the fixture afterward rather than treating the mutation as real data.

The Data Analytics with Generative AI programme covers SQL, data quality and reporting pipelines. This example illustrates how they connect: a join choice is also a decision about which business records a stakeholder gets to see.

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 COUNT star versus COUNT column in a customer report.
• Continue with Find missing records with SQL anti-joins.