Joins & aggregates
Code Lab45 min
Joins & aggregates
A JOIN follows a foreign key to another table:
SELECT a.name AS author, COUNT(b.id) AS books
FROM authors a
LEFT JOIN books b ON b.author_id = a.id
GROUP BY a.name
HAVING COUNT(b.id) > 1
ORDER BY books DESC;Read it inside-out: join, then group rows by author, then drop groups the HAVING rejects, then sort. LEFT JOIN keeps authors with zero books — COUNT(b.id) (not COUNT(*)) counts only real books, so empty authors show 0.
Joins are the whole point of a relational database. Every query you will ever write in NestJS is this lesson wearing a coat.
Exercise
List every author with their book count, including authors with none, highest count first. Then repeat but only for books priced under $25.
Practice this lesson in the IDE
Open the Database Design practice workspace, edit the starter code, and run your work in the browser.
Open IDE and practice