A SQL join you can actually debug

A join is a question about relationships. Before writing one, say the cardinality out loud: one course has many lessons; one lesson belongs to one course. If that sentence is wrong, the query will be wrong even if it runs.

select c.slug, c.title, count(l.id) as lessons
from courses c
left join lessons l on l.course_slug = c.slug
group by c.slug, c.title
order by lessons desc;

Three checks when a join looks wrong

• Count the source rows before and after the join. A row count that explodes usually means a one-to-many relationship was treated as one-to-one.
• Use LEFT JOIN when the right side is optional, and move right-side filters into the join condition if you need to preserve unmatched rows.
• Check NULLs explicitly with IS NULL. Do not compare with = NULL.

Write the query in two steps: first inspect the joined rows without aggregation, then add GROUP BY. Debugging aggregates without seeing the underlying rows is guessing with extra confidence.