LEFT JOIN SQL: NULLs, Filters, Duplicates, and Fixes

What does LEFT JOIN do in SQL?

SELECT
  c.customer_id,
  c.customer_name,
  o.order_id,
  o.order_date
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id;

This answers “show all customers and any orders they have.” It does not promise one row per customer. A customer with five orders returns five rows; a customer with none returns one null-extended row. State the intended grain before choosing columns or aggregates.

Think in preserved rows and matching pairs

A reliable mental model has two phases. First, evaluate the join condition and return every matching pair, just like an inner join. Second, add one null-extended row for each left row that found no match. Conditions in ON participate in the first phase; conditions in WHERE filter the joined result afterward.

Place right-side filters in ON or WHERE intentionally

Filter placement is the most common LEFT JOIN bug. If a right-side eligibility rule belongs to what counts as a match, place it in ON. If the final answer should contain only joined rows satisfying a condition, put it in WHERE and recognize that unmatched rows will usually disappear.

-- All customers, plus completed orders when available
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
 AND o.status = 'completed';

-- Only customers that have a completed order
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
WHERE o.status = 'completed';

The second query rejects null-extended rows because NULL = 'completed' is not true. It may be better written as an inner join to make intent obvious. Adding OR o.status IS NULL is not always equivalent to moving the condition: a customer with only pending orders has matched rows, so no null-extended row exists to preserve.

The official PostgreSQL joined-table documentation demonstrates how a restriction in ON versus WHERE changes an outer join's result.

Distinguish unmatched rows from real NULL values

After a left join, a nullable right-side attribute cannot prove whether a match exists. If orders.discount_code is naturally nullable, discount_code IS NULL includes both unmatched customers and matched orders without a discount. Test a right-side primary key or another column guaranteed non-null for real rows.

COALESCE is useful for display but can erase the distinction between missing and zero. A customer with no invoice is not necessarily a customer with a zero-value invoice. Preserve a match indicator when downstream decisions need that distinction.

Prevent one-to-many LEFT JOIN multiplication

LEFT JOIN preserves left rows, but it does not preserve left row count. One customer with four addresses produces four result rows. Joining another one-to-many table can multiply combinations. This is correct relational behavior and a common source of inflated counts and sums.

WITH ranked_addresses AS (
  SELECT
    a.*,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY effective_from DESC, address_id DESC
    ) AS rn
  FROM customer_addresses a
)
SELECT c.customer_id, a.city
FROM customers c
LEFT JOIN ranked_addresses a
  ON a.customer_id = c.customer_id
 AND a.rn = 1;

This restores zero-or-one address per customer only if “latest” is the correct rule. Other needs may require pre-aggregation, returning a collection, or retaining every child row at child grain. Do not use DISTINCT as a reflex; it can hide duplicated projections without repairing semantics.

Use LEFT JOIN as an anti-join carefully

To find customers with no orders, left join orders and keep rows where a non-nullable order key is null. NOT EXISTS often communicates the existence question more directly and cannot multiply output rows.

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

SELECT c.customer_id
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

When the question is “no completed orders,” the status condition belongs inside the join or EXISTS subquery. Putting it in the outer WHERE and also testing null creates contradictory logic. Test customers with no orders, only pending orders, and at least one completed order.

Keep preservation clear across several LEFT JOINs

A chain of left joins can create a false sense that the first table is always fully preserved. A later inner join to a nullable child can remove rows. A WHERE predicate on any later table can reject null-extended records. Two one-to-many branches can multiply each other. Draw the relationship graph and label expected cardinality for every edge.

1. Choose the preserved population. State why every row from the first stage belongs.
2. Add one relationship. Measure row count, distinct preserved keys, and unmatched rate.
3. Validate right-side uniqueness. Enforce or profile the key expected to be zero-or-one.
4. Reduce child detail. Aggregate or rank before joining when final output stays at parent grain.
5. Inspect downstream filters. Mark predicates that can reject null-extended rows.
6. Reconcile metrics. Compare totals before and after each relationship.

Review LEFT JOIN performance without changing meaning

Optimizers can choose hash, merge, or nested-loop algorithms and may reorder operations where semantics allow. Outer-join preservation restricts some transformations. Pushing a predicate into the right input may be safe when it defines eligible matches; pushing a null-rejecting predicate can effectively change the join. Validate results before celebrating a faster plan.

Validate LEFT JOIN results before production

• Assert every required left key appears at least once.
• Test no match, one match, several matches, null keys, and malformed keys.
• Compare ON and WHERE placement against a written business question.
• Measure total rows, distinct left keys, and match counts after each join.
• Use a guaranteed non-null right key when detecting unmatched rows.
• Reconcile sums and counts before and after one-to-many relationships.
• Inspect actual plans with representative skew and unmatched rates.

Review LEFT JOIN behavior with adversarial data

A production-safe review starts by naming the preserved population in plain language. “Every active customer at month end” is testable; “customers joined to orders” is not. Capture a stable control count for that population before the join. After each relationship, compare distinct preserved keys, total rows, matched keys, unmatched keys, and maximum right-side matches. These measurements separate legitimate child detail from accidental loss or multiplication.

Create fixtures that force the edge cases: a left row with no partner, one partner, and several partners; a right row whose descriptive attribute is genuinely null; duplicate right keys; null join keys; status changes at the reporting boundary; and a child row outside the eligible date range. Run the intended query and an independently written control query. If both are derived from the same flawed join, agreement proves little, so use direct counts or source-system invariants where possible.

Performance review comes after semantic proof. Inspect whether the right input is wider or larger than expected, whether estimates understand key skew, and whether a safe eligibility filter can reduce it before hashing or sorting. Preserve a reconciliation query and the edge-case fixture in automated tests. A faster left join is not an improvement if it changes who remains, what null means, or how many times a measure is counted.

Record the unmatched rate as a monitored contract rather than a one-time observation. A sudden rise can indicate delayed ingestion, changed identifiers, a broken effective-date condition, or a new source population. Alert on both loss and unexpected improvement: a match rate that becomes exactly 100 percent may mean a downstream filter has silently removed the very records the outer join was intended to preserve.

LEFT JOIN SQL frequently asked questions

Continue with a related guide

Use these closely related guides to verify the next layer of the workflow without mixing separate troubleshooting or decision intents.

• SQL joins
• INNER JOIN in SQL