Joins and Aggregation in SQL

Introduction

Most useful questions need data from several tables, and summary numbers that are easy to get wrong. This lesson covers joining carefully and aggregating honestly. Outputs are from real runs on the library data.

Inner and left joins

• INNER JOIN keeps only rows with a match on both sides.
• LEFT JOIN keeps all rows from the left table, with empty values where there is no match.

Which books were never borrowed? A left join from books to loans, then keep the unmatched:

q("""SELECT b.title
     FROM books b
     LEFT JOIN loans l ON l.book_id = b.id
     WHERE l.id IS NULL""")

In this dataset every book was borrowed at least once, so the result is empty. The pattern is the standard way to find "things with no matching records". An inner join could never show them, because unmatched rows disappear.

Average loan length by genre

SQLite stores dates as text, and julianday() converts a date to a number of days, so you can subtract:

q("""SELECT b.genre,
            ROUND(AVG(julianday(l.returned_on) - julianday(l.loaned_on)), 1) AS avg_days
     FROM loans l
     JOIN books b ON b.id = l.book_id
     WHERE l.returned_on IS NOT NULL
     GROUP BY b.genre
     ORDER BY avg_days DESC""")

  genre  avg_days
  Crime      19.7
   Home      18.2
Fiction      15.9
   Food      12.8

Notes:

• The WHERE ... IS NOT NULL leaves out books still out, which have no return date.
• Crime books were kept longest on average, and Food the shortest.
• But how many loans are behind each average? With few loans, a difference of a few days could be chance. Add COUNT(*) to show it.

Double counting

A common trap: joining a table to another that has several matching rows multiplies the rows. If you then sum a value that belongs to the first table, it gets counted several times.

Prevent it by:

1. Knowing the grain of each table (what one row represents).
2. Aggregating before joining when needed.
3. Checking totals: after a join, does the row count and total still match what you expect?

For example, total loans are 30, and by-city counts of 18, 11 and 1 add to 30, which confirms nothing was lost or doubled.

Fair comparisons

Raw counts favour bigger groups. Compare rates:

q("""SELECT m.city,
            COUNT(DISTINCT m.id) AS members,
            COUNT(l.id) AS loans,
            ROUND(1.0 * COUNT(l.id) / COUNT(DISTINCT m.id), 1) AS loans_per_member
     FROM members m
     LEFT JOIN loans l ON l.member_id = m.id
     GROUP BY m.city
     ORDER BY loans_per_member DESC""")

This lists, for each city, how many members there are, their total loans and the loans per member. Leeds has three members and 18 loans (6.0 each). Working it out makes you ask whether the comparison is fair before you claim "Leeds borrows most".

Notice 1.0 * to force decimal division, and COUNT(DISTINCT ...) to count each member once.

Good habits

• Test queries on small cases where you know the answer.
• Name columns clearly with AS.
• Format SQL so it is readable, and keep it in files, not only in a notebook.
• Check row counts before and after each join.

Try it yourself

Write a query that shows, for each member, their name, number of loans, and number of loans still out. Check that the "still out" values add up to 3.

Summary

• INNER JOIN keeps matches only, and LEFT JOIN keeps every row from the left table so you can find missing matches.
• Use date functions to calculate lengths, exclude missing values on purpose and show the count behind each average.
• Beware double counting after joins, and compare rates, such as loans per member, rather than raw totals.