Querying

Every query here runs against the catalogue you built in lesson 1.

Objectives

After this unit, learners can:

  • Join tables and say which rows a join keeps and which it drops.
  • Group rows and aggregate them correctly.

Why This Matters

The librarian asks which authors have never been borrowed. The data is all there, spread over four tables; a query that joins them the wrong way returns a confident, wrong list.

Joins

An inner join keeps the rows that match on both sides and drops the rest. A left join keeps every row on the left, filling the right side with NULL where nothing matches. Say which rows you expect before you run either.

SELECT b.title, a.name
FROM book b
JOIN book_author ba ON ba.book_id = b.id
JOIN author a       ON a.id = ba.author_id;

Change the query so books with no author still appear.

Answer

Make both joins LEFT JOIN: the second must be left too, or it drops the rows the first one kept.

Grouping and Aggregation

GROUP BY folds the rows that share a value into one, and an aggregate such as COUNT summarises each fold. WHERE filters rows before grouping; HAVING filters the groups after.

SELECT a.name, COUNT(*) AS titles
FROM author a
JOIN book_author ba ON ba.author_id = a.id
GROUP BY a.name
ORDER BY titles DESC;

Keep only the authors with more than two titles.

Answer

Add HAVING COUNT(*) > 2 after the GROUP BY; an aggregate cannot go in WHERE.

Worked Example

The problem. Which authors have never been borrowed?

The approach. Start from every author, left-join through to the loans, and keep the authors whose loan count is zero.

SELECT a.name
FROM author a
LEFT JOIN book_author ba ON ba.author_id = a.id
LEFT JOIN loan l         ON l.book_id = ba.book_id
GROUP BY a.name
HAVING COUNT(l.id) = 0;

Reading the result. COUNT(l.id) counts loans, not rows: an author with no loans still has one row, with NULL loan columns, and counts zero.

Common Problems

Counting the wrong thing

COUNT(*) after a left join counts the unmatched row too. Count a column from the joined side instead.

ERROR: aggregate functions are not allowed in WHERE

Move the condition to HAVING.

Summary

  • Inner joins drop unmatched rows; left joins keep the left side and fill the gaps with NULL.
  • WHERE filters rows before grouping, HAVING filters groups after; count a column, not *, after a left join.

Knowledge Check

  1. Which rows does an inner join drop?

    Answer

    Those with no match on the other side.

  2. When does a left join change a COUNT(*)?

    Answer

    When a left row has no match: it still produces one row, which COUNT(*) counts.

  3. What is the difference between WHERE and HAVING?

    Answer

    WHERE filters rows before grouping; HAVING filters groups after.

  4. Why qualify column names in a multi-table query?

    Answer

    Two tables can share a column name; qualifying says which one you mean.

  5. How would you check a join has not multiplied rows?

    Answer

    Compare its row count with the count before the join.

Further Reading