Database DesignSQL basicsLesson 27 / 69

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