SQL JOINs without double-counting: keys, grain, NULLs, and safer totals

Reviewed October 1, 2026. All people, orders, and amounts below are synthetic teaching data. Examples use standard SQL constructs documented by PostgreSQL and DuckDB and were computationally checked with SQLite. The complete CTE query is designed for Noetrion's DuckDB-based SQL Playground.

A SQL query can execute successfully and still answer the wrong question. One common cause is a join that changes the number of rows representing an order, customer, or event. The database follows your instructions correctly; the aggregation then counts the same logical amount more than once.

Before memorizing join types, learn two questions: what does one row mean? and how many matching rows can exist on the other side? These questions are often more useful than a diagram of overlapping circles.

Start with the grain and the relationship

The grain is the unit represented by one row. A customer table may contain one row per customer, an order table one per order, and a payment table one per payment event. A customer can have several orders, and an order can have several payment events. Those are different grains.

PostgreSQL's constraint documentation distinguishes primary keys, unique constraints, and foreign keys. A primary key identifies a row; a foreign key checks a reference. A foreign key from orders to customers does not imply that each customer has only one order.

A complete join you can run

Paste the following into the SQL Playground. The CTEs create temporary query inputs without changing the existing sample dataset. For later snippets, keep the WITH block and replace only its final SELECT.

WITH
customers(customer_id, name) AS (
  VALUES (1, 'Nia'), (2, 'Arif'), (3, 'Lina')
),
orders(order_id, customer_id, status, amount) AS (
  VALUES (101, 1, 'paid', 100),
         (102, 1, 'pending', 50),
         (103, 2, 'paid', 80)
),
payments(payment_id, order_id, paid_amount) AS (
  VALUES (201, 101, 60), (202, 101, 40), (203, 103, 80)
)
SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

The result has four rows: Nia appears twice, Arif once, and Lina once with missing order fields. Nia's repetition is expected. It becomes a bug only if a later step treats each output row as a unique customer.

Join types choose which unmatched rows survive

PostgreSQL's table-expression rules define joins in terms of matching rows. An inner join emits matching pairs. A left join also retains unmatched left rows and fills the right-side fields with NULL.

A full outer join is useful for reconciling both sides when records may be missing from either source. It is not inherently a better or more complete answer; the required population determines the join.

ON and WHERE are not interchangeable after a left join

-- Keep every customer; attach paid orders when they exist.
SELECT c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id AND o.status = 'paid'
ORDER BY c.customer_id;

-- Keep only joined rows whose status is paid.
SELECT c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.status = 'paid'
ORDER BY c.customer_id;

Run these separately with the shared CTE block. The first returns Nia, Arif, and Lina. The second returns only Nia and Arif. Lina's unmatched NULL status does not satisfy the WHERE predicate. Neither query is universally correct: “all customers with their paid orders” and “customers having paid orders” are different requests.

COUNT(*) and COUNT(column) ask different questions

PostgreSQL's aggregate reference specifies that COUNT(*) counts input rows while COUNT(expression) counts non-null values. After the left join, Lina has one output row but no order identifier.

SELECT c.name, COUNT(*) AS joined_rows,
       COUNT(o.order_id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;

Expected pairs are Nia: 2, 2; Arif: 1, 1; Lina: 1, 0. Use a non-nullable key from the matched table when counting matches. Counting an optional descriptive field can undercount records that exist but have missing descriptions.

The fan-out problem: an order becomes several rows

Join orders to payments and order 101 appears twice, because it has two payment events. Summing o.amount across those rows produces 100 + 100 + 80 = 280, not the paid orders' original value of 100 + 80 = 180.

Order 101
Amount 100→Two payments
60 and 40→Two joined rows
100 repeated→Wrong sum
200 for this order

SUM(DISTINCT o.amount) is not a general fix. Two different orders can legitimately have the same amount, and that function would collapse their values. Distinct values are not distinct business entities.

Aggregate each side to the required grain first

For one row per customer, calculate each customer-level measure independently, then join those summaries. This version selects paid orders; it does not claim to implement an accounting revenue-recognition policy.

-- Append these CTEs to the shared WITH block above.
, order_totals AS (
  SELECT customer_id, SUM(amount) AS paid_order_value
  FROM orders WHERE status = 'paid'
  GROUP BY customer_id
), payment_totals AS (
  SELECT o.customer_id, SUM(p.paid_amount) AS received
  FROM orders AS o
  JOIN payments AS p ON p.order_id = o.order_id
  GROUP BY o.customer_id
)
SELECT c.name,
       COALESCE(ot.paid_order_value, 0) AS paid_order_value,
       COALESCE(pt.received, 0) AS received
FROM customers AS c
LEFT JOIN order_totals AS ot ON ot.customer_id = c.customer_id
LEFT JOIN payment_totals AS pt ON pt.customer_id = c.customer_id
ORDER BY c.customer_id;

The result is Nia: 100, 100; Arif: 80, 80; Lina: 0, 0. DuckDB's aggregate documentation explains why a missing sum can be NULL. Here, COALESCE maps absence to zero because that is the intended meaning of these two measures—not because every missing value should become zero.

Use EXISTS when you only need membership

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

This returns each qualifying customer once. EXISTS tests whether a subquery returns any row. DuckDB also documents semi and anti joins for membership and non-membership. Avoid expanding records when the question only asks whether a relationship exists.

A review checklist before publishing a total

1. Write the grain of every input and the desired output.
2. Check key uniqueness at that grain, not merely whether a column is called “id.”
3. Predict the join's row count, then compare it with the actual count.
4. Inspect unmatched keys and a customer with multiple child records.
5. Reconcile totals before and after every grain-changing step.
6. Test two different records with equal amounts to catch false distinct-value fixes.

In pandas, merge(validate=...) can check declared one-to-one or many-to-one relationships. Its documentation also warns that null keys can match each other, unlike ordinary SQL equality joins. Similar syntax across tools does not guarantee identical missing-value behavior.

The durable lesson is simple: join correctness is about relationships and units of observation. A plausible-looking total is not enough; make the row-count and grain assumptions inspectable.