SQL Joins and Unexpected Row Multiplication

The mental model that keeps joins predictable is blunt: for every row on the left, the join emits one output row for each row on the right that satisfies the ON condition. Zero matches means zero rows (for an inner join). Three matches means three rows. The database doesn’t know that you thought of the right-hand table as a lookup with one answer per key.

That’s what “cardinality” means here: how many right-hand rows each left-hand row matches. One-to-one keeps the row count. One-to-many grows it, on purpose. The trouble is joins you believe are one-to-one that aren’t, and this guide walks through one with real numbers. The queries were run on PostgreSQL 16.15; nothing here is PostgreSQL-specific.

A currency conversion that’s 27% too high

Orders are in several currencies, and finance wants the USD total. There’s a table of daily exchange rates:

CREATE TABLE orders (
  id int PRIMARY KEY, currency text NOT NULL,
  order_date date NOT NULL, amount numeric(10,2) NOT NULL
);
CREATE TABLE fx_rates (
  currency text NOT NULL, rate_date date NOT NULL,
  usd_rate numeric(10,4) NOT NULL, source text NOT NULL
);

INSERT INTO orders VALUES
  (1,'EUR','2026-09-01',100), (2,'EUR','2026-09-02',50),
  (3,'GBP','2026-09-01',80),  (4,'INR','2026-09-01',5000),
  (5,'EUR','2026-09-02',20);

INSERT INTO fx_rates VALUES
  ('EUR','2026-09-01',1.08,'ecb'),
  ('EUR','2026-09-02',1.09,'ecb'),
  ('EUR','2026-09-02',1.0905,'manual'),
  ('GBP','2026-09-01',1.27,'ecb');

Someone added a manual EUR rate on 2 September as an override, and nobody has loaded INR rates yet. Both are the kind of thing that happens in real reference data. The obvious query:

SELECT count(*) AS joined, sum(o.amount * r.usd_rate) AS usd
FROM orders o
JOIN fx_rates r ON r.currency = o.currency AND r.rate_date = o.order_date;

 joined |    usd
--------+------------
      6 | 362.235000

Five orders went in and six rows came out. If you’re checking, that’s suspicious but not alarming, since it’s off by one. Work out the right answer by hand for the orders that have rates: 100 × 1.08 + 50 × 1.09 + 80 × 1.27 + 20 × 1.09 = 285.90. The query says 362.24, which is 27% higher, and it’s still missing the INR order entirely.

Two problems that hide each other

Look at the rows behind the sum:

SELECT o.id, r.source, r.usd_rate
FROM orders o
JOIN fx_rates r ON r.currency = o.currency AND r.rate_date = o.order_date
ORDER BY o.id, r.source;

 id | source | usd_rate
----+--------+----------
  1 | ecb    |   1.0800
  2 | ecb    |   1.0900
  2 | manual |   1.0905
  3 | ecb    |   1.2700
  5 | ecb    |   1.0900
  5 | manual |   1.0905

Orders 2 and 5 match two rates each, so they appear twice and their amounts are converted and summed twice. That’s multiplication. Order 4 doesn’t appear at all, because an inner join keeps only rows that found a match. That’s loss.

Two extra rows plus one missing row gives a net change of +1, which is why the count looked almost normal. On a real table with millions of rows, a few thousand duplicates and a few thousand drops can cancel to a row count that’s within a rounding error of the input. Never treat “the count looks about right” as proof that the join is right.

Finding duplicate keys before they find you

When you plan to join on a set of columns, check that they’re unique on the side you expect to be “one”:

SELECT currency, rate_date, count(*)
FROM fx_rates
GROUP BY currency, rate_date
HAVING count(*) > 1;

 currency | rate_date  | count
----------+------------+-------
 EUR      | 2026-09-02 |     2

And check for left rows with no match by switching to a LEFT JOIN and looking for NULLs on the right: SELECT o.* FROM orders o LEFT JOIN fx_rates r ON ... WHERE r.currency IS NULL returns order 4. A LEFT JOIN of the same tables returns 7 rows: the 6 from before plus the unmatched INR order with a NULL rate.

Those two checks, “is the key unique?” and “does every row match?”, are the whole diagnostic toolkit. Run them whenever you join to a table someone else maintains.

Fixing it: decide the rule, then enforce it

The SQL fix depends on a business decision: when there’s an ECB rate and a manual rate, which wins? Say finance chooses ECB, and orders without a rate must show up rather than vanish:

SELECT count(*) AS n, sum(o.amount * r.usd_rate) AS usd
FROM orders o
LEFT JOIN fx_rates r
  ON r.currency = o.currency
 AND r.rate_date = o.order_date
 AND r.source = 'ecb';

 n |    usd
---+------------
 5 | 285.900000

Five rows in, five rows out, and the total matches the hand calculation. The INR order is present with a NULL rate, so sum skips it; a real report should show it as “unconverted” instead of letting it disappear from the total. The source filter sits in the ON clause, not in WHERE, so it can’t turn the LEFT JOIN back into an inner join. The left join filter placement guide covers that trap.

If the rule is “manual overrides ECB”, filtering won’t do it. Pick one row per key first, for example with DISTINCT ON (currency, rate_date) ... ORDER BY currency, rate_date, (source = 'manual') DESC, or row_number() as described in ranking and ties, and join to that.

Then make the assumption impossible to break. If the table should have one rate per currency, day and source:

ALTER TABLE fx_rates ADD PRIMARY KEY (currency, rate_date, source);

Now a second ECB rate for the same day is rejected at insert time, and the join with source = 'ecb' is guaranteed one-to-one. Queries written against an unenforced assumption break the day the data changes. Queries written against a constraint keep working.

Other ways joins multiply rows

• A missing join column. Drop the date condition and join only on currency: EUR orders (3) times EUR rates (3) plus GBP (1 × 1) gives 10 rows. It’s not a full cross join, but it’s the same mechanism. Composite keys need every column in the ON clause.
• Joining on a non-key column. Joining customers to a CRM export on email assumes emails are unique in the export. They often aren’t (shared inboxes, case differences).
• Many-to-many through a bridge table. Tags, roles, categories: joining products to tags gives one row per product-tag pair, which is right for “list tags” and wrong for “sum product revenue”.
• Two independent one-to-many joins on one parent. The fan-out case, covered in aggregation and result grain.

And NULL keys never match in an = join, so rows with a NULL foreign key drop out of inner joins without any warning.

Interview practice: the user count that doubled after a migration

A dashboard query SELECT count(*) FROM users u JOIN user_profiles p ON p.user_id = u.id used to return the number of users. After a migration it returns roughly double. Nothing else changed in the query. What happened, how would you prove it, and what’s the lasting fix?

Answer

The join can only return more rows than users if some users match more than one profile, so user_profiles.user_id is no longer unique. The likely cause is that the migration re-inserted profiles instead of upserting them, or backfilled without deleting the old rows.

To prove it, run SELECT user_id, count(*) FROM user_profiles GROUP BY user_id HAVING count(*) > 1 and look at a few duplicated users side by side to see which copy is current. Also compare count(*) from users alone against the join, and check the reverse direction with a LEFT JOIN ... WHERE p.user_id IS NULL for users who lost their profile, since a botched migration can do both.

The lasting fix is to deduplicate (keeping the newest profile per user, or whatever the product rule is) and then add UNIQUE (user_id) or make user_id the primary key of user_profiles. The dashboard query was fine; it relied on a one-to-one relationship that the schema never enforced. Also, count(*) of users doesn’t need the join at all, so I’d simplify it to SELECT count(*) FROM users.

Keep going

• SQL and PostgreSQL interview questions
• SQL and PostgreSQL MCQs
• SQL aggregation and result grain
• SQL left joins and filter placement
• Official docs: Joined tables in PostgreSQL