Why JOIN Multiplies Rows: Matching Keys, Constraints and Output Grain
Diagnose repeated JOIN rows by counting matching keys, checking constraints and choosing whether the result should contain orders, items or summaries.
On this page
The short answer
A JOIN returns matching pairs, so one source row can legitimately appear several times when it has several partners. Count occurrences of the actual join key on both sides, including any input filters. For an equality join, a non-null key appearing L times on one side and R times on the other contributes L times R matching pairs. Check uniqueness constraints separately from the values observed today. Then choose the intended output grain: detail rows, one row per parent, or aggregates. A larger row count alone does not identify the relationship, and the chosen join algorithm does not prove duplication.
What a JOIN counts
A JOIN describes which pairs of source rows belong in the result. For an INNER JOIN, each pair satisfying the ON condition contributes one output row. An outer join can additionally preserve unmatched rows with NULLs on the other side. The Cartesian-product description is a logical explanation, not a claim that the engine physically constructs every possible pair. PostgreSQL chooses an execution plan. Later WHERE filtering can remove rows from the joined result, so inspect the complete query when comparing counts.
Cartesian products and repeated keys
A CROSS JOIN pairs every row of its left input with every row of its right input. Two inputs containing three rows each therefore produce nine pairs. An equality join retains only pairs whose keys match. For a particular non-null key appearing L times on the left and R times on the right, an equality INNER JOIN contributes L times R pairs. Repeated keys on both sides can create many-to-many multiplication. One-to-many matching alone does not imply that the result exceeds the larger input.
WITH t1(num) AS (VALUES (1), (2), (3)),
t2(num) AS (VALUES (1), (3), (5))
SELECT t1.num AS left_num, t2.num AS right_num
FROM t1 CROSS JOIN t2;
-- Nine pairs in this illustrative dataset.A self-contained one-to-many example
An INNER JOIN emits one result row for each matching pair. In the example, order 1 matches item 10 and item 11; order 2 matches item 12. The three output rows are valid relationships, not duplicate records that should automatically be deleted. This is the usual one-to-many pattern when the order identifier is unique in orders and can repeat in order_items. Decide the intended output grain first: one row per order, one row per item, or a summary. That decision determines whether the multiplication is useful or requires aggregation.
WITH orders(order_id) AS (VALUES (1), (2)),
order_items(item_id, order_id) AS (VALUES (10, 1), (11, 1), (12, 2))
SELECT o.order_id, i.item_id
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
ORDER BY o.order_id, i.item_id;
-- Three pairs: order 1 appears with two different items.LEFT JOIN and unmatched rows
A LEFT JOIN preserves an unmatched left row by supplying NULL for the right-hand columns. A left row with two right-hand matches instead produces two result rows. In the example, left keys 1 and 3 match, while key 2 has no partner, giving three output rows. This describes the join result before later filtering. A WHERE condition on a right-hand column can remove unmatched rows because their right values are NULL. Include those conditions in the investigation instead of assuming every complete query containing LEFT JOIN preserves the original left input.
WITH t1(num) AS (VALUES (1), (2), (3)),
t2(num) AS (VALUES (1), (3), (5))
SELECT t1.num AS left_num, t2.num AS right_num
FROM t1 LEFT JOIN t2 ON t1.num = t2.num
ORDER BY t1.num;
-- Three rows; the right value for left key 2 is NULL.Count the actual matching keys
Count occurrences of the actual join key on both sides, after applying the relevant input filters. The example uses three item rows: order 1 has two items and order 2 has one. Grouping by order_id reveals the repeated child key. Exclude NULL keys when investigating ordinary equality matches, since NULL does not equal NULL in that condition. A sample containing one row per key does not establish a uniqueness constraint or prove a permanent one-to-one relationship. Check the schema separately; future inserts can change observed counts.
WITH order_items(item_id, order_id) AS (VALUES (10, 1), (11, 1), (12, 2))
SELECT order_id, COUNT(*) AS item_count
FROM order_items
WHERE order_id IS NOT NULL
GROUP BY order_id
HAVING COUNT(*) > 1;
-- Order 1 has two items in this illustrative dataset.Foreign keys, uniqueness and NULL
A foreign key links referencing columns to a referenced key whose uniqueness is enforced. It does not make the referencing values unique: several items may refer to the same order unless another constraint prevents it. Nullable referencing values can be permitted; a foreign key alone does not require every row to contain a parent identifier. The sample schema separately declares order_id NOT NULL, making that requirement explicit. Compare the actual join columns with the declared constraints, especially when the query joins a composite key or adds other conditions.
CREATE TABLE orders (
order_id integer PRIMARY KEY
);
CREATE TABLE order_items (
item_id integer PRIMARY KEY,
order_id integer NOT NULL REFERENCES orders(order_id)
);Choose the required output grain
When several detail tables are joined, inspect each relationship separately. Combining multiple child collections can repeat one child's values across the other collection and make totals misleading. If the required output is one row per order, aggregate the detail table to that grain before joining it. The example counts items per order and joins that smaller result to orders. Its INNER JOIN includes only orders that have items; use LEFT JOIN and an explicit zero policy when orders without items must also appear. DISTINCT removes repeated projected rows, but does not establish the correct business grain.
SELECT o.order_id, i.item_count
FROM orders AS o
JOIN (
SELECT order_id, COUNT(*) AS item_count
FROM order_items
GROUP BY order_id
) AS i ON i.order_id = o.order_id;Compare three join types on explicit data
The final example defines three left keys and three right keys with VALUES, making the dataset self-contained. Only keys 1 and 3 match, so the INNER JOIN yields two rows. The earlier LEFT JOIN example includes left key 2 with a NULL right value, yielding three rows. The CROSS JOIN example has all nine possible pairs. These are illustrative counts derived from the listed values, not a benchmark or a report of executed tests. Change one input to contain a repeated matching key and reason about which additional pairs will appear.
WITH t1(num) AS (VALUES (1), (2), (3)),
t2(num) AS (VALUES (1), (3), (5))
SELECT t1.num
FROM t1 INNER JOIN t2 ON t1.num = t2.num
ORDER BY t1.num;
-- Two rows: matching keys 1 and 3.Things to check
- Count occurrences of the actual join key on both inputs after applying their filters; exclude NULL when diagnosing ordinary equality matches.
- Inspect primary, unique and foreign-key constraints separately; observed unique values do not establish a schema guarantee.
- Check for repeated keys on both sides: L matching left rows and R matching right rows produce L times R equality-join pairs for that key.
- Use EXPLAIN for estimates, then verify matching-key counts; a nested-loop or other join algorithm alone does not prove duplication.
Where this applies
The examples describe equality joins and PostgreSQL syntax. NULL does not match NULL under ordinary equality; other conditions can produce different relationships. Composite keys must be considered as complete tuples. Actual output also depends on WHERE filtering, grouping and projection. The listed sample counts are illustrative, and no execution speed or production correctness is inferred from them.