Joins & relationships

A single table can only answer questions about itself. The real power of a relational database shows up when you stop keeping everything in one giant table and instead split data into focused tables that point at each other through keys. An authors table holds people, a books table holds titles, and a foreign key links each book back to its author. Nothing is duplicated, and yet you can still ask questions that span both.

This module is about answering those cross-table questions. You will learn to stitch tables back together with joins, to collapse many rows into summary numbers with aggregates, to nest one query inside another with subqueries and CTEs, and finally to compute per-row rankings and running totals with window functions.

The example schema

Every lesson reuses three small tables. authors and books should look familiar from the CRUD module; we add a sales table so there is something to aggregate and rank.

CREATE TABLE authors (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  born int
);

CREATE TABLE books (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title     text NOT NULL,
  author_id bigint REFERENCES authors (id),
  published int
);

CREATE TABLE sales (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  book_id  bigint REFERENCES books (id),
  region   text NOT NULL,
  copies   int NOT NULL
);

await pool.query(`
  CREATE TABLE sales (
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    book_id bigint REFERENCES books (id),
    region  text NOT NULL,
    copies  int NOT NULL
  )
`);

cur.execute("""
    CREATE TABLE sales (
      id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      book_id bigint REFERENCES books (id),
      region  text NOT NULL,
      copies  int NOT NULL
    )
""")

_, err := conn.Exec(ctx, `
  CREATE TABLE sales (
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    book_id bigint REFERENCES books (id),
    region  text NOT NULL,
    copies  int NOT NULL
  )`)

sqlx::query(
    "CREATE TABLE sales (
       id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
       book_id bigint REFERENCES books (id),
       region  text NOT NULL,
       copies  int NOT NULL
     )",
)
.execute(&pool)
.await?;

In pgAdmin: paste the three CREATE TABLE statements into the Query Tool and run them. The new tables appear under Schemas then Tables, and if you expand a table you can see the foreign-key constraint listed under Constraints.

How the tables relate

Each table connects to the next through a key column. A book carries the id of its author, and a sale carries the id of its book. These links are what every query in this module follows.

flowchart LR
  A[authors] -->|id referenced by books.author_id| B[books]
  B -->|id referenced by sales.book_id| S[sales]

Tables linked by keys

Read the arrows as “is referenced by”. One author can have many books, and one book can have many sales rows — a classic one-to-many shape that joins were designed to traverse.

What this module covers

Joins — combining rows from two or more tables on matching key columns, and choosing whether unmatched rows are kept or dropped.
Aggregates and GROUP BY — collapsing many rows into counts, sums, and averages, then filtering those groups with HAVING.
Subqueries and CTEs — using the result of one query inside another, and naming intermediate steps with WITH for readability.
Window functions — ranking rows and computing running totals without throwing away the individual rows.

Tips / gotchas

A foreign key documents a relationship and enforces it, but it is the join in your query that actually brings the related rows together.
The same question can often be written as a join, a subquery, or a window function; learning all three lets you pick the clearest one.
Keep a mental picture of the one-to-many links above; almost every query in this module walks one of those arrows.