Joins and Aggregation, Beyond the Basics
Going past the SELECT/JOIN/GROUP BY fundamentals from the SQL module: multi-table joins, HAVING vs. WHERE, and aggregate functions that hold up in both SQLite and PostgreSQL.
What you'll learn
- Join three or more tables correctly in a single query
- Explain the difference between WHERE and HAVING, and when each applies
- Use aggregate functions (COUNT, SUM, AVG, MIN, MAX) correctly with GROUP BY
Prerequisites
Explanation
This lesson's exercises run in this sandbox's real SQL runner, which is SQLite, not PostgreSQL. The specific SQL in this lesson — standard JOIN syntax, GROUP BY, HAVING, and the core aggregate functions — is genuinely dialect-compatible between SQLite and PostgreSQL, so what you build and run here behaves the same way in a real PostgreSQL database. Later lessons in this module (transactions, indexes, roles) cover PostgreSQL-specific behavior that SQLite can't honestly demonstrate, and switch to a different format for exactly that reason.
Joining three or more tables is just chaining additional JOIN ... ON ... clauses — orders JOIN books ON orders.book_id = books.id JOIN authors ON books.author_id = authors.id connects orders to the authors of the books they contain, through an intermediate table neither side references directly. Each join condition only needs to correctly relate the two tables it's directly joining; the chain as a whole correctly propagates the relationships through every intermediate step.
WHERE filters rows before grouping; HAVING filters groups after aggregation — this is the entire distinction, and it's why WHERE COUNT(*) > 5 is invalid (at the point WHERE runs, individual rows haven't been grouped yet, so there's no COUNT(*) to reference), while HAVING COUNT(*) > 5 is exactly the right tool for "only show authors with more than 5 books," applied after GROUP BY has produced one row per author. A query can use both together: WHERE narrows which rows are even considered before grouping (WHERE published_year > 2000), and HAVING then filters the resulting groups (HAVING COUNT(*) > 2).
The core aggregate functions — COUNT, SUM, AVG, MIN, MAX — each collapse a group of rows into a single value, and each has a well-known, worth-remembering edge case: COUNT(*) counts rows including NULLs, but COUNT(column) counts only rows where that specific column is non-NULL; AVG, SUM, MIN, and MAX all silently ignore NULL values entirely rather than treating them as zero, which matters for correctness whenever a column can genuinely be absent — an average computed while silently skipping NULLs can look correct at a glance while quietly excluding exactly the rows a report was supposed to account for.
Example
Genuinely runs here — this SQL sandbox is SQLite, and this JOIN/GROUP BY/HAVING syntax is standard and dialect-compatible with PostgreSQL.
-- Three-table join: which authors have books that have actually been ordered, with total quantity?
SELECT authors.name, SUM(orders.quantity) AS total_ordered
FROM orders
JOIN books ON orders.book_id = books.id
JOIN authors ON books.author_id = authors.id
GROUP BY authors.id
HAVING SUM(orders.quantity) > 2
ORDER BY total_ordered DESC;Try it yourself
Change HAVING SUM(orders.quantity) > 2 to a different threshold and see how the result set changes.
Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.
Guided exercise
Guided exercise
Write a query joining books to authors, returning each book's title and its author's country, only for books published after the year 2000.
Checks: Returns each qualifying book's title with its author's country
Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.
Stuck? Get a hint.
Independent exercise
Independent exercise
Write a query joining orders to books to authors, returning each author's name and the total quantity of their books ordered, but ONLY for authors whose total ordered quantity exceeds 2 -- sorted by total quantity descending, then by author name ascending as a tiebreaker (order matters for this check).
Checks: Returns only authors exceeding the threshold, correctly aggregated and sorted
Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.
Stuck? Get a hint.
Common mistakes
- Writing WHERE COUNT(*) > 5 instead of HAVING COUNT(*) > 5 -- WHERE runs before grouping, when there's no aggregated COUNT(*) to reference yet; this is a genuine SQL error, not just a style issue.
- Assuming AVG(column) treats NULL values as 0 -- it doesn't; NULLs are excluded entirely from the average's denominator, which can silently change the result in ways that don't match the intended calculation.
- Chaining JOINs with an incorrect or missing ON condition on one of the joins -- this silently produces a cross-product-like explosion of rows (every row of one table paired with every row of the other) rather than an error, which can be hard to notice if you don't check the row count.
Knowledge check
Takeaway
WHERE filters rows before grouping; HAVING filters groups after aggregation — using the wrong one either fails outright (WHERE referencing an aggregate) or silently returns the wrong rows (HAVING conditions that should have been WHERE, filtering too late to be efficient or, in some cases, too late to be correct).
Summary
Multi-table joins chain ON conditions through intermediate tables. WHERE filters individual rows pre-aggregation; HAVING filters groups post-aggregation. COUNT(*) counts all rows; COUNT(column) counts only non-NULL values; AVG/SUM/MIN/MAX all silently exclude NULLs rather than treating them as zero.
References
Your notes
Notes save automatically.
Finished this lesson?
Mark it complete to track your progress and schedule a future review.
AI tutor
The optional AI tutor isn't enabled in this deployment. All lessons, exercises, quizzes, and search work fully without it.