Normalised Tables, Joins, and Grouped Answers

By the end of this lesson you can move repeated data into its own table, join it back on a shared key, and say which rows an inner join removes from a report before anyone reads it.

Storing the full agency name on every notice looks harmless with three notices. With four thousand it means the same text repeated four thousand times, three different spellings of it, and no single place to correct one.

Splitting it out fixes all three at once, and introduces the operation that puts it back together. Joins are also where reports acquire their most invisible defect: an inner join silently drops anything without a match on both sides, so "agencies and their notice counts" quietly becomes "agencies that published at least one notice", which is a different report with the same title.

Normalisation, at the level this project needs, means each fact lives in one place. The agency's name belongs on an agency row; each notice carries only the agency's short code.

A foreign key is a column holding another table's key. Declaring it, agency_code TEXT NOT NULL REFERENCES agencies(agency_code), documents the relationship and lets the database refuse a notice pointing at an agency that does not exist. SQLite only enforces that when foreign-key support is switched on for the connection, with PRAGMA foreign_keys = ON, which is worth doing in any project that relies on it.

A join matches rows from two tables on a condition:

SELECT a.name, n.notice_id
FROM agencies a
JOIN notices n ON n.agency_code = a.agency_code

The short aliases, a and n, keep the rest of the query readable, and qualifying each column with its table makes it obvious where every value comes from.

Two kinds matter here:

• JOIN (an inner join) keeps only rows that match on both sides. An agency with no notices disappears; a notice with an unrecognised agency code disappears.
• LEFT JOIN keeps every row from the left table, filling the right-hand columns with NULL where there is no match. The agency with no notices survives, with nothing attached.

Choosing between them is a reporting decision, not a technical one. "Which agencies published work we can bid on" wants an inner join. "How much did each agency publish this month, including the ones that published nothing" needs a left join, because zero is the answer you are looking for.

GROUP BY collapses matched rows into one row per group, and the aggregate functions, COUNT, SUM, AVG, MIN, MAX, summarise each group. Three details are worth memorising:

• COUNT(*) counts rows; COUNT(column) counts rows where that column is not NULL. After a left join those differ, and the second is usually what you want.
• SUM of no rows is NULL, not 0. Wrap it in COALESCE(SUM(...), 0) when a report needs a number.
• GROUP BY promises no particular order. Add ORDER BY whenever the order matters, which for a report is always.

WHERE filters rows before grouping; HAVING filters groups after. "Only Oregon notices" is a WHERE; "only agencies with more than two notices" is a HAVING.

import sqlite3

connection = sqlite3.connect(":memory:")
connection.executescript(
    """
    CREATE TABLE agencies (agency_code TEXT PRIMARY KEY, name TEXT NOT NULL);
    CREATE TABLE notices (
        notice_id TEXT PRIMARY KEY,
        agency_code TEXT NOT NULL REFERENCES agencies(agency_code),
        amount INTEGER
    );
    INSERT INTO agencies VALUES ('GSA', 'General Services Administration');
    INSERT INTO agencies VALUES ('VA', 'Veterans Affairs');
    INSERT INTO notices VALUES ('A-1', 'GSA', 300000);
    INSERT INTO notices VALUES ('A-2', 'GSA', 100000);
    """
)

inner = connection.execute(
    "SELECT a.name, COUNT(n.notice_id) FROM agencies a"
    " JOIN notices n ON n.agency_code = a.agency_code"
    " GROUP BY a.name ORDER BY a.name"
).fetchall()

outer = connection.execute(
    "SELECT a.name, COUNT(n.notice_id) FROM agencies a"
    " LEFT JOIN notices n ON n.agency_code = a.agency_code"
    " GROUP BY a.name ORDER BY a.name"
).fetchall()

print(inner)
print(outer)

Two tables, two agencies, two notices, both belonging to the same agency. executescript runs several statements at once, which is convenient for setting up a schema.

The two queries differ by one word. Everything else, the grouping, the counting, the ordering, is identical.

COUNT(n.notice_id) rather than COUNT(*) is deliberate. In the left join, the Veterans Affairs row is produced with every notice column NULL, so COUNT(*) would count that one phantom row and report 1. Counting these non-null notice IDs ignores the unmatched agency’s placeholder row. To count all matches in general, choose a right-hand column guaranteed non-null on every matched row.

Predict both printed lines.

[('General Services Administration', 2)]
[('General Services Administration', 2), ('Veterans Affairs', 0)]

In the outer query, change COUNT(n.notice_id) to COUNT(*) and predict the second printed line.

[('General Services Administration', 2), ('Veterans Affairs', 1)]

An assistant was asked for the total value published by each agency, including agencies with nothing. It produced this.

rows = connection.execute(
    "SELECT a.name, SUM(n.amount) AS total FROM agencies a"
    " LEFT JOIN notices n ON n.agency_code = a.agency_code"
    " WHERE n.amount > 0"
    " GROUP BY a.name"
).fetchall()

for name, total in rows:
    print(f"{name}: {total:,}")

LEFT JOIN notices n ON n.agency_code = a.agency_code AND n.amount > 0

Two normalised tables and one agency that has published nothing. Report each agency that has published at least one notice, with its count and total value.

Loading this exercise…

Optional: rerun your query as a LEFT JOIN with COALESCE(SUM(n.amount), 0), and put the two results side by side. Then write one sentence for each, stating the question that result correctly answers. Being able to name the question a report answers, rather than the one its title implies, is the most useful habit in this module.

Sign in to track your progress on this exercise.

1. What does an inner join leave out that a left join keeps?
2. Why prefer COUNT(column) over COUNT(*) after a left join?
3. What does SUM return for a group with no matching rows, and how do you fix it?
4. What is the difference between WHERE and HAVING?

Sign in to track your progress on this exercise.

Repeated facts belong in their own table; foreign keys enforce the link when enforcement is enabled. An inner join omits unmatched rows, while a left join preserves unmatched left rows. Count a non-null match identifier and use COALESCE where zero is intended. A WHERE predicate that rejects null-extended rows can remove the rows a left join preserved. Next: how a value gets into a query safely, and how a schema changes without losing what is already stored.