162: SQL - Joins and Relational Design

Learning outcomes

• choose inner and outer joins by required semantics;
• model many-to-many relationships;
• detect accidental row multiplication.

Practice

Add labels and a task_labels join table. Query tasks with labels, users with zero tasks, and label counts without leaking another user's data. Compare one join query with an N+1 application loop and inspect duplicate rows.

Join reasoning

Joining tasks to labels changes one task row into one row per matching label. If the API wants one task with an array of labels, aggregate after enforcing the owner predicate:

sqlCopy
SELECT t.id, t.title, coalesce(array_agg(l.name ORDER BY l.name) FILTER (WHERE l.id IS NOT NULL), '{}') AS labels
FROM tasks t
LEFT JOIN task_labels tl ON tl.task_id = t.id
LEFT JOIN labels l ON l.id = tl.label_id
WHERE t.owner_id = $1
GROUP BY t.id, t.title;

Check cardinality before and after each join. A missing join predicate can silently create a Cartesian product.

Checkpoint

Explain why a LEFT JOIN can become an inner join when filtered incorrectly, and how grouping changes result cardinality.

References

• PostgreSQL: Table Expressions
• PostgreSQL: Joins