SQL: Joining Tables and Summarising Groups
Pulling rows from two tables together on a matching key, and collapsing many rows into one answer with COUNT, SUM and GROUP BY. This is the payoff for having split the tables in the first place.
What a learner can do afterwards
- Join two tables on a foreign key and say which rows the join drops
- Count or total rows per group and read the result table
- Explain why a join with no matching condition returns far too many rows
1 · Read
Relational tables are built to be recombined. A foreign key in one table points at the primary key of another, and a join follows those pointers to assemble matching rows. An inner join drops rows with no match on either side, which is why members without loans disappear from a loan count.
A library joins Members to Loans on the member key, then counts the loans per member. GROUP BY collapses each member's rows into one summary row carrying the count. Read the result by asking what one row now represents: one member and their loan count, not one loan. A member with no loans has no row to join with, so an inner join leaves them out entirely.
COUNT tallies rows while SUM adds values, and both return one answer per group. Saying which rows a join drops is part of reading any result. If you want members with zero loans to appear, you need an outer join that keeps unmatched rows.
Sketch the tables and their links before you write the join, then the condition writes itself. A join with no matching condition pairs every row with every row, which is how a small library returns millions of nonsense rows. Splitting avoids repeated facts, and joins reassemble them for each question you ask.
Join on the keys, group what matches, and always ask which rows the join dropped.
2 · Watch
Take it off screen
Where it sits
8 questions wait behind this lesson, each with its answer explained. Every answer feeds the sky: stars light as they are learned, and dim when it is time to come back.