Core SQL Concepts

The ORM types the query. Postgres still evaluates a relation: rows in, rows out, in a fixed logical order. Schema, RLS, and migrations are the wiring — Backend APIs with Hono, Drizzle, Zod OpenAPI, and SST and Building a Multi-Tenant Backend with Hono, Better Auth, Drizzle, and Postgres RLS. This note is the statement.

The examples share one shop. Constraints live in the CREATE TABLE. Indexes wait until cost.

CREATE TABLE customers (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email      text NOT NULL UNIQUE,
  name       text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id uuid NOT NULL REFERENCES customers (id),
  status      text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id   uuid NOT NULL REFERENCES orders (id),
  sku        text NOT NULL,
  qty        integer NOT NULL CHECK (qty > 0),
  unit_cents integer NOT NULL CHECK (unit_cents >= 0),
  UNIQUE (order_id, sku)
);

Pattern Map

1. Relation in, relation out

A SELECT is an expression that takes relations and returns a relation — not a procedure that visits a table.

• A table is a named relation: a bag of rows, each row a tuple of columns. The shape of the result is decided by the statement.
• A key is a constraint on which rows can exist, not a hint to the planner. PRIMARY KEY identifies the row. UNIQUE on customers.email forbids duplicates. REFERENCES forbids an order that names a missing customer.
• NULL is not a value. It is the absence of one. Comparison with NULL yields unknown, and WHERE keeps only true.
• Failure: WHERE email = NULL is never true; the predicate is IS NULL. NOT IN (SELECT …) becomes a trap when the subquery can return NULL. NOT EXISTS is the anti-join that still means what it says.

2. Logical evaluation order

Postgres does not run a statement left to right as written. It runs a logical pipeline. Physical execution — hash vs nested loop, index vs seq scan — is a later choice that must preserve this meaning.

FROM / JOIN → WHERE → GROUP BY → HAVING → SELECT → WINDOW → ORDER BY → LIMIT

• FROM / JOIN produce the working row set. WHERE filters rows. GROUP BY collapses groups; HAVING filters groups. SELECT names columns. Windows compute without collapsing further. ORDER BY sorts. LIMIT cuts.
• A WITH CTE is a named subquery plugged into FROM. It is not a different engine.
• A SELECT alias is invisible to WHERE. A window function cannot appear in WHERE or HAVING — those clauses have already run.
• Failure: WHERE order_count > 0 or WHERE n = 1 on a window alias. Filter on a window with a subquery or a CTE — another FROM.

3. JOIN

A join is how FROM builds a wider row. Inner keeps matches. Left keeps every left row and fills the right side with NULL when there is no match. Anti-join keeps left rows that have no match.

SELECT c.email, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

SELECT c.email
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
);

• A left join duplicates a customer once per order. Aggregating after that join without grouping counts the customer as many times as they have orders.
• EXISTS / NOT EXISTS stop at the first match and leave the outer row intact.
• Failure: LEFT JOIN … WHERE right.id IS NULL as the first-reflex anti-join. It duplicates, and NULL comparison is easy to get wrong. JOIN plus DISTINCT undoes a cardinality explosion you introduced.

4. Aggregation

GROUP BY is the collapse. After WHERE, remaining rows are partitioned by the grouping keys. Each group becomes one output row.

SELECT
  c.id,
  c.email,
  count(o.id) AS order_count,
  coalesce(sum(oi.qty * oi.unit_cents), 0) AS revenue_cents
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
LEFT JOIN order_items oi ON oi.order_id = o.id
GROUP BY c.id, c.email
HAVING count(o.id) >= 1;

• Ungrouped columns cannot appear in SELECT unless they are wrapped in an aggregate — Postgres rejects the statement rather than pick an arbitrary value.
• HAVING is WHERE for groups. WHERE cannot see count(*). HAVING can.
• count(o.id) ignores NULLs from the left join, so customers with no orders are 0 and drop out of HAVING. count(*) would count the empty joined row as one.
• Failure: treating count(col) and count(*) as synonyms. The aggregate you pick is a statement about which rows exist.

5. Windows

A window function computes across related rows without collapsing them. PARTITION BY is the group. ORDER BY inside OVER is the order inside that group. The result is one value per input row.

SELECT
  o.customer_id,
  o.id AS order_id,
  row_number() OVER (
    PARTITION BY o.customer_id
    ORDER BY o.created_at DESC, o.id DESC
  ) AS recency
FROM orders o;

• Aggregation answers “what is the sum for this customer?” Windows answer “what is this row’s rank among that customer’s orders?” and still return every order.
• row_number() is the essential window. Filter recency = 1 in an outer query to get the latest order per customer. A running sum() OVER (PARTITION BY … ORDER BY …) is the other half: a frame that grows with each row.
• Aggregate line items before the window so they do not explode the row set. Neither window belongs in WHERE.

6. Patterns

Four production shapes on the same shop: exists, keyset pagination, upsert, top-N-per-group.

Exists. “Has at least one shipped order” is a semi-join. EXISTS stops at the first match and leaves the outer row intact.

SELECT c.email
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
    AND o.status = 'shipped'
);

Keyset pagination. OFFSET n still walks n rows. A keyset seek starts after the last row you already have. The sort key must be unique — (created_at, id) — or rows that share a timestamp will skip or repeat.

SELECT id, customer_id, created_at
FROM orders
WHERE (created_at, id) < (:cursor_created_at, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Upsert. Check-then-insert races. ON CONFLICT is one statement against a unique key the schema already declared.

INSERT INTO customers (email, name)
VALUES ('ada@example.com', 'Ada')
ON CONFLICT (email) DO UPDATE
SET name = excluded.name
RETURNING id;

Top-N-per-group. GROUP BY cannot keep the non-grouped columns of the “top” row without a second pass. Number the rows, then keep n <= 2.

SELECT customer_id, order_id, created_at
FROM (
  SELECT
    o.customer_id,
    o.id AS order_id,
    o.created_at,
    row_number() OVER (
      PARTITION BY o.customer_id
      ORDER BY o.created_at DESC, o.id DESC
    ) AS n
  FROM orders o
) ranked
WHERE n <= 2;

7. Cost

An index is a data structure that makes a lookup cheap and a write a little more expensive. It is not a constraint.

CREATE INDEX orders_customer_id_created_at_idx
  ON orders (customer_id, created_at DESC, id DESC);

EXPLAIN SELECT id, status
FROM orders
WHERE customer_id = '11111111-1111-1111-1111-111111111111';

-- Index Scan using orders_customer_id_created_at_idx
--   Index Cond: (customer_id = '11111111-…')

• The unique index that enforces customers.email is both constraint and access path. A plain index on orders.customer_id is only an access path. The type map — B-tree, hash, LSM, geospatial, inverted — is DB Indexing in System Design.
• Without that path, WHERE customer_id = $1 is a sequential scan: instant on a laptop, linear in production.
• The same composite index covers a keyset seek: equality on customer_id, walk created_at DESC, id DESC.
• Failure: guessing from local data and shipping a seq scan. EXPLAIN checks the access path you meant. EXPLAIN (ANALYZE) checks it against real row counts.

8. Failure

Two requests read qty, both add one, both write. Each transaction looks correct. The row skipped a number. Isolation did not fail mysteriously — the database was never told to make the change atomic.

-- lost update if two sessions do: read qty, add 1, write qty
UPDATE order_items
SET qty = qty + 1
WHERE id = :id;

• A forgotten unique constraint is a data bug that no amount of careful WHERE will fix after the fact.
• Check-then-act is a lost update. Use UPDATE … SET qty = qty + 1, or a UNIQUE that rejects the duplicate insert. Check-then-insert races; ON CONFLICT is one statement.
• The schema is the API. Application filters are a habit.
• Isolation, RLS, and tenant context: Building a Multi-Tenant Backend with Hono, Better Auth, Drizzle, and Postgres RLS. Typed schema, pool, and migrations: Backend APIs with Hono, Drizzle, Zod OpenAPI, and SST.

Recap Q&A