Lesson 3 · Foundations

Querying: SELECT, JOIN, aggregate

Reading data — the SQL you'll see in every repository.

Your win: read and write the everyday query — filter with WHERE, combine tables with JOIN, summarise with GROUP BY — matching the real queries in spike and conversationmgmt.

SELECT: choose columns, filter rows

SELECT subject, status
FROM   emails
WHERE  status = $1 AND deleted_at IS NULL
ORDER  BY created_at DESC
LIMIT  20;

Read it as: pick these columns, from this table, keep rows matching WHERE, sort, and cap the count. Values are always passed as parameters ($1), never string-concatenated — that's how our repos avoid SQL injection.¹

Your real spike query filters by an id list and the soft-delete flag:

internal/spike/.../repositories/email.go:196

SELECT ... FROM emails e
WHERE  e.email_id = ANY($1)          -- match any id in the array param
  AND  e.deleted_at IS NULL;         -- exclude soft-deleted rows

JOIN: combine rows across tables

An email has many recipients in a separate table. A JOIN stitches them together on the shared key (the foreign key from Lesson 1):

SELECT e.subject, r.recipient_address
FROM   emails e
JOIN   email_recipients r ON r.email_id = e.email_id   -- INNER JOIN
WHERE  e.deleted_at IS NULL;

JOIN types in one line INNER — only rows with a match on both sides (the default). LEFT — all left rows, with NULLs where the right has no match. (RIGHT/FULL are the mirror/both.) 90% of joins are INNER or LEFT.

Aggregate: summarise with GROUP BY

Aggregate functions (count, sum, avg, …) collapse many rows into one; GROUP BY makes one result per group:

SELECT status, count(*) AS n
FROM   emails
WHERE  deleted_at IS NULL
GROUP  BY status
HAVING count(*) > 5;         -- HAVING filters groups (WHERE filters rows)

Anchor conversationmgmt counts filtered results by wrapping the query: SELECT COUNT(*) FROM ( … ) tmp_count (support_conversation.go CountByFilter) — a common pattern for "how many match this filter?" alongside the paginated list. And every read you'll see ends with deleted_at IS NULL — the soft-delete convention.

Read / practise this next

PostgreSQL Tutorial — SELECT, joins & aggregates

Example-driven pages for each clause. Then the official docs' Queries chapter for the precise semantics.

→ postgresqltutorial.com (SELECT / joins / GROUP BY)
→ postgresql.org/docs — Queries

Check yourself (from memory)

Q1. An INNER JOIN returns rows where…

INNER keeps only matched pairs. LEFT would keep all left rows, NULL-filling unmatched right columns.

Q2. GROUP BY is used together with…

It produces one row per group for aggregates. HAVING filters groups; WHERE filters rows first.

Q3. Why do our reads always end with deleted_at IS NULL?

Rows are soft-deleted (marked, not removed), so reads must filter them out. (The tenant filter is RLS — Part 6.)

Write (from memory) a query for subjects of active emails matching a set of ids, and explain the pieces.

recall, then click to reveal

SELECT subject FROM emails WHERE email_id = ANY($1) AND
    deleted_at IS NULL;

— SELECT subject picks the column; FROM emails the table; WHERE email_id = ANY($1) filters to the id list passed as one array parameter (never string-concatenated);

AND deleted_at
    IS NULL

excludes soft-deleted rows. To add recipients: JOIN email_recipients r ON r.email_id = e.email_id. To count per status: GROUP BY status with count(*).

Want subqueries, CTEs (WITH … AS), or window functions — all of which our conversationmgmt queries use? Ask me.

1. PostgreSQL — Queries; repo rule: values via $N params (.claude/rules/security.md).