Lesson 04

Join cardinality

Review joins for one-to-one, one-to-many, and missing-row behavior before trusting the result.

joins
cardinality
duplicates

Good Code

review-summary.sql

WITH comment_counts AS (
  SELECT
    review_id,
    COUNT(*) AS comment_count
  FROM review_comments
  GROUP BY review_id
)
SELECT
  reviews.id,
  reviews.title,
  COALESCE(comment_counts.comment_count, 0) AS comment_count
FROM code_reviews AS reviews
LEFT JOIN comment_counts
  ON comment_counts.review_id = reviews.id
WHERE reviews.author_id = $1;

Bad Code

review-summary.sql

SELECT
  reviews.id,
  reviews.title,
  review_comments.id AS comment_id
FROM code_reviews AS reviews
JOIN review_comments
  ON review_comments.review_id = reviews.id
WHERE reviews.author_id = $1;

Review Notes

What to review

Good Code

The good version pre-aggregates comments and uses a left join so reviews without comments still appear once.

Bad Code

The bad version turns each review into one row per comment and drops reviews that have no comments. That may silently corrupt pagination, counts, and UI summaries.

Takeaways

A join changes row count, so review the relationship before reviewing the selected columns.