Joins Without Duplicate Surprises

A join emits one output row for every matching pair. If one order matches two line items, that order appears twice. This is correct relational behavior, but it becomes a defect when an analyst assumes the original grain survived.

Prerequisites

Know table grain, keys, and NULL behavior from the previous lessons.

Learning goals

• Predict join cardinality from key uniqueness and relationship shape.
• Distinguish inner, left, cross, semi, and anti joins.
• Detect fanout before aggregating measures.
• Use EXISTS when the question is about existence rather than matched detail.

Count before and after

Before joining, state the intended output grain and test whether the join key is unique on the side expected to contribute at most one row. A one-to-many join from orders to items deliberately changes the grain from order to order item. Summing orders.amount after that join would count order 102 twice.

Outer joins preserve unmatched rows from one or both sides and fill the absent side with NULL. Conditions on the right table belong in the ON clause when unmatched left rows must remain.

Worked example

This Standard SQL query makes fanout visible by counting item matches at order grain. COUNT(i.sku) returns zero for the unmatched cancelled order; COUNT(*) would count its left-join placeholder row as one.

SELECT o.order_id, COUNT(i.sku) AS item_rows
FROM orders AS o
LEFT JOIN order_items AS i ON i.order_id = o.order_id
GROUP BY o.order_id
ORDER BY o.order_id;

order_id	item_rows
101	1
102	2
103	1
104	0
105	1

When only membership matters, EXISTS avoids producing a row per matching order.

SELECT c.customer_id, c.customer_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;

customer_id	customer_name
1	Ada
2	Ben
3	Cy

DuckDB also supports SEMI JOIN and ANTI JOIN syntax. These are DuckDB-specific syntax choices for concepts expressible portably with EXISTS and NOT EXISTS.

Limitations and common mistakes

• DISTINCT can hide unexpected fanout without repairing the wrong grain.
• Joining on a name instead of a key can create both false matches and missed matches.
• Many-to-many joins can grow multiplicatively; aggregate each side to the required grain before joining when appropriate.
• A join condition that omits one component of a composite key is incomplete.

Practice

Compute item-level revenue as quantity * unit_price, then aggregate to order grain before joining it to orders. Verify that order 102 totals 60.00 and appears once.

Next steps

Now make the output grain explicit with grouping and aggregation.

Primary sources

Checked 2026-10-14: DuckDB FROM and joins, PostgreSQL table expressions and joined tables, and ISO SQL model guidance.