Join Cardinality

Definition

Join cardinality describes the numerical RELATIONSHIP between two joined tables — one-to-one, one-to-many, or many-to-many — and directly determines how many rows the join produces relative to its inputs.

How It Works

• One-to-one: each row in A matches AT MOST one row in B (e.g. each employee has at most one passport record) — the join's row count stays close to the smaller/filtered table's size.
• One-to-many: each row in A can match MANY rows in B (e.g. each department has many employees) — joining departments to employees multiplies rows: the result has one row PER EMPLOYEE, not per department, because each department row is repeated once for every matching employee.
• Many-to-many: rows in A can match many rows in B AND VICE VERSA (e.g. students and courses, via an enrollments junction table) — typically implemented via an intermediate junction/bridge table with two one-to-many relationships, rather than a direct many-to-many join.

Edge Cases and Pitfalls

• A one-to-many join's row-count multiplication is easy to forget when computing an aggregate afterward: SUM(d.budget) after joining departments to employees would incorrectly sum a department's budget once PER EMPLOYEE (since the department row is duplicated per matching employee row) rather than once per department — a classic "fan-out" bug (already flagged conceptually in 7.1/DQL).
• Understanding which side of a relationship is the "many" side is essential for predicting a join's row count BEFORE running it — a genuinely useful sanity check when a query returns a suspiciously large or small number of rows.
• Enforcing intended cardinality at the schema level (e.g. a UNIQUE constraint to guarantee a true one-to-one relationship) prevents data-quality drift into an accidental one-to-many that the application logic didn't expect.

Key Takeaways

• Join cardinality (1:1, 1:N, N:M) describes how many matches to expect on each side, and directly predicts the joined result's row count.
• A one-to-many join multiplies the "one" side's rows — aggregating afterward without accounting for this is the classic "fan-out" bug.
• Many-to-many relationships are typically implemented via a junction table with two one-to-many relationships, not a direct join.