SQL LEFT JOIN: Put Conditions in ON or WHERE Without Losing Rows

A report must display every customer, including those without a paid order. After adding a status filter, some customers disappear. The query can still contain LEFT JOIN while expressing the wrong result set.

The fixture tests a specific rule: retain every customer and attach only paid orders. Run its table-creation statements in a test database, not production.

1. Include three different customer cases

CREATE TABLE customers (id INTEGER PRIMARY KEY);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL
);

INSERT INTO customers (id) VALUES (1), (2), (3);
INSERT INTO orders (id, customer_id, status) VALUES
  (10, 1, 'pending'),
  (11, 1, 'paid'),
  (12, 2, 'pending');

Customer 1 has pending and paid orders, customer 2 only pending, and customer 3 none. Having no orders differs from having orders that fail the condition. A test containing only customers 1 and 3 can miss the bug.

2. WHERE filters the joined result

SELECT c.id, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.status = 'paid'
ORDER BY c.id, o.id;

Only customer 1 and order 11 remain. Customer 2's matched order is not paid. Customer 3's null-extended order columns do not make the status comparison true.

PostgreSQL Table Expressions explains matching conditions versus post-join restrictions for outer joins. This is a semantic model, not a promise about fixed physical execution order.

If the actual requirement is only customers with a paid order, filtering may be correct. The bug is the mismatch with the requirement to retain everyone.

3. Put order eligibility in ON

SELECT c.id, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
 AND o.status = 'paid'
ORDER BY c.id, o.id;

The result is (1, 11), (2, NULL), (3, NULL). Customer 2 receives a null-extended row because no order satisfies the complete matching condition.

A left-side filter, such as selecting active customers only, can still belong in WHERE. Separate customer eligibility from related-order eligibility rather than mechanically moving every predicate into ON.

4. OR order_id IS NULL is not a general repair

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

This returns customers 1 and 3 but still loses customer 2. The join already matched customer 2 with order 12, so there is no null-extended row. WHERE rejects the pending order, and the join does not create a replacement row afterward.

Ask reviewers to state expected results for all three fixture cases. Seeing some rows reappear on a screen is insufficient proof of correctness.

5. Count related records correctly

SELECT c.id,
       COUNT(*) AS joined_rows,
       COUNT(o.id) AS paid_orders
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
 AND o.status = 'paid'
GROUP BY c.id
ORDER BY c.id;

joined_rows is 1 for each customer; paid_orders is 1, 0, 0. COUNT(*) includes the placeholder row, whereas COUNT(o.id) excludes NULL. Count a right-side key guaranteed non-null for an actual record rather than an optional business field. See PostgreSQL Aggregate Functions.

Joining another one-to-many relationship can multiply each order. COUNT(o.id) does not automatically fix that fan-out. Inspect cardinality and consider aggregating relationships separately according to the metric required.

6. Test IDs and aggregates

The queries in both language versions were executed with in-memory PDO SQLite. Checks distinguish WHERE, ON and OR IS NULL, then test an empty orders table and an additional paid order for customer 1. No on-disk or production database was changed.

• Assert ordered customer/order pairs.
• Cover customers with no orders and customers with only ineligible orders.
• Add multiple eligible orders to verify intended row growth.
• Assert zero aggregate values rather than customer presence alone.
• For multi-tenant data, check complete join conditions and scope.

This small experiment verifies semantics, not PostgreSQL execution plans or performance. Repeat validation on the actual engine with the real schema, data and indexes before deployment.

7. A practical review sequence

1. State which primary entities must be retained.
2. Separate primary-entity filters from related-record eligibility.
3. Inspect every WHERE reference to the right table and explain NULL behavior.
4. Check fan-out before COUNT or SUM.
5. Establish correct results with a fixture before tuning performance.

LEFT JOIN does not guarantee that every left-side row survives all later filters. Distinguishing pair matching from final-row eligibility keeps reports aligned with their business rules.