Query toolbox

Shape of a maintainable query

SELECT
  o.id,
  c.email,
  o.total_cents,
  o.placed_at
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
  AND o.placed_at >= $2
ORDER BY o.placed_at DESC, o.id DESC
LIMIT $3;

This query specifies output, join condition, parameters, deterministic ordering, and a bound. The driver binds $1, $2, and $3; never concatenate user input into SQL.

Choosing a join form

Goal	Use
Keep matching rows from both sides	`INNER JOIN` / `JOIN`
Keep every row on the left	`LEFT JOIN`
Test whether a related row exists	`EXISTS`, often clearer than join-plus-`DISTINCT`
Find rows without a relation	`NOT EXISTS`, avoiding `NOT IN` null traps

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

Aggregates and windows differ

GROUP BY collapses rows. Window functions retain detail rows while calculating across a window.

SELECT
  customer_id,
  id AS order_id,
  total_cents,
  row_number() OVER (
    PARTITION BY customer_id
    ORDER BY placed_at DESC, id DESC
  ) AS recency_rank,
  sum(total_cents) OVER (PARTITION BY customer_id) AS lifetime_cents
FROM orders;

What CTEs are for

A CTE names a stage in a complex query; it is not an automatic optimization switch.

WITH recent_paid AS (
  SELECT customer_id, total_cents
  FROM orders
  WHERE status = 'paid'
    AND placed_at >= now() - interval '30 days'
)
SELECT customer_id, sum(total_cents) AS paid_cents
FROM recent_paid
GROUP BY customer_id;

Pagination

Prefer keyset pagination for large result sets:

SELECT id, placed_at, total_cents
FROM orders
WHERE (placed_at, id) < ($1, $2)
ORDER BY placed_at DESC, id DESC
LIMIT 50;

Unlike a large OFFSET, this does not repeatedly skip earlier rows and behaves more predictably under concurrent inserts. The cursor must contain every ordering key.

Pre-flight checklist

Is the output contract explicit and minimal?
Can any join multiply rows?
Is NULL semantics intentional?
Does ordering have a unique final tie-breaker?
Does the driver bind every input value?
Do you need statement timeout and a result limit?