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
- Predict the size of an unfiltered CROSS JOIN and explain how a matching condition changes its pairs
1 · Read
Normalisation separated facts into tables. SQL can combine them again. These examples use SQLite. Members.member_id is a primary key; Loans.member_id refers to it. A join applies its condition rather than automatically following a foreign key. INNER JOIN returns matching pairs and omits unmatched rows. Equality with NULL is not true, so a loan with NULL member_id does not match a member.
Members contains IDs 11, 12 and 13, named Imani, Leo and Noor. Loans 41 and 42 belong to 11; loan 43 belongs to 12. SELECT Members.name, Loans.loan_id FROM Members INNER JOIN Loans ON Members.member_id = Loans.member_id; returns Imani with 41 and 42, and Leo with 43. Noor has no match. Table.column identifies which table supplies a column.
SELECT Members.member_id, COUNT(*) FROM Members INNER JOIN Loans ON Members.member_id = Loans.member_id GROUP BY Members.member_id; returns 11 with 2 and 12 with 1. GROUP BY makes one group per member ID. Names may be shared, so group by ID. COUNT(*) counts rows; COUNT(column) counts non-NULL values. SUM(fine) adds non-NULL fines per group. For fines 3, 4 and 4, the count is 3 and the sum is 11.
LEFT JOIN keeps every row from its left table. Unmatched members get NULL loan fields. SELECT Members.member_id, COUNT(Loans.loan_id) FROM Members LEFT JOIN Loans ON Members.member_id = Loans.member_id GROUP BY Members.member_id; includes Noor with zero. Actual loan_id values are non-NULL primary keys. COUNT(Loans.loan_id) ignores an unmatched row's NULL; COUNT(*) would count that row as one.
SELECT * FROM Members CROSS JOIN Loans; requests all columns and every possible pair. With m and n input rows, this unfiltered CROSS JOIN returns m times n rows. Three members and four loans give 12 pairs. A matching condition can remove unwanted pairs. CROSS JOIN can also be intentional. A large result alone does not prove a condition was forgotten.
Choose a join condition, group by member ID, and count the values your question needs.
SQL lesson, questions and study cards written for this dataset. The query examples use SQLite.
Readings and study cards written for this dataset. Linked videos are credited to Khan Academy: "Welcome to SQL" and "S-Q-L or SEQUEL?". Video reuse terms have not been verified.
More to read
Follow the written join condition
A member's name can be stored once in Members while their member ID appears on several Loans rows. A join can put the name beside each matching loan without storing the name on every loan.
Read ON Members.member_id = Loans.member_id as a test of a proposed pair. Equal non-NULL values pass. Different values do not. A loan with a NULL member ID does not pass this equality test.
An inner join returns passing pairs. A member with two matching loans appears in two result rows. A member with no matching loan appears in none.
A foreign key describes a relationship and may constrain stored values. The query still needs a join form and condition. Joins are not limited to declared foreign keys. Here, equal member IDs identify the pairs needed for the library's loan list.
Identify what each group represents
Choose the group identifier before calculating a summary. For loan counts per member, member_id identifies the member. A name alone may combine loans belonging to different people who share that name.
Suppose member ID 27 has three loan rows with fines 1, 4 and 6. COUNT(*) for that group is 3. SUM(fine) is 11. The count answers how many rows; the sum answers how much the listed fines total.
GROUP BY makes a group for each distinct value of its grouping columns. Selecting member_id and COUNT(*) can return one summary row per member ID. Read that row as an ID with a count, rather than an individual loan.
COUNT(column) counts only non-NULL values in that column. COUNT(*) counts every row in the group.
Keep members whose loan count is zero
To include every member, start from Members and use LEFT JOIN Loans on equal member IDs. A member without a loan still has a result row. Its loan fields are NULL because no loan supplied those values.
Group by Members.member_id and use COUNT(Loans.loan_id). Actual loan IDs are non-NULL primary-key values, so each matching loan contributes one. The unmatched row's NULL contributes nothing, giving a count of zero.
COUNT(*) would count the unmatched row itself and give one for that member. Keeping the member and counting their loans are separate choices in the query.
SUM ignores NULL values too, but its empty-value result is different. In SQLite, SUM(fine) returns NULL when a group has no non-NULL fine values. A preserved member does not automatically receive a fine total of zero.
Count every possible pair
An unfiltered CROSS JOIN combines each row of one table with each row of another. Four members and seven loans produce 4 × 7 = 28 pairs. Each member appears beside all seven loans, whether or not those loans belong to that member.
For a library loan list, equality between member IDs selects the relevant pairs. A Cartesian product can also be intentional when a question asks for every combination.
Inspect the query before diagnosing a large result. The output count does not reveal whether a condition was omitted. A written condition might be true for every pair. Other join forms can supply conditions through their syntax. Name the actual join form and any filter when predicting its size.
Count the possible pairs in a small example before running a larger query.
Explore more
2 · Watch
En anglaisTake 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.