Join Cardinality

Join cardinality concerns how many rows a join produces. For an equality key with two matching rows on the left and three on the right, an inner join produces six pairs. Matching one row on each side is a condition to verify, not a property of JOIN syntax.

An order worth 40 dollars joined to three item rows appears three times. Summing the repeated order amount gives 120 dollars. SUM(DISTINCT amount) is not a repair: two separate 40-dollar orders would then be counted as only 40. Aggregate item information to one row per order before joining when the intended result has order grain.

INNER JOIN drops rows without matches. LEFT JOIN retains unmatched left rows and supplies NULLs for the right side; RIGHT JOIN reverses that role and FULL OUTER JOIN preserves unmatched rows on both sides. A NULL customer country can also belong to a matched customer. Test a non-NULL right-side key to distinguish no customer record from an unknown country.

Before and after joining, check key uniqueness, unmatched keys, output grain, and comparable totals. Equal row counts alone are insufficient because a dropped row and a duplicated row can offset each other. Keep currency and filters constant when reconciling monetary totals.

Further reading

• DuckDB Documentation, FROM and JOIN Clauses

See the worked examples in SQL from Zero to Working.