SQL Query Recipes: Joins, Grouping, Window Functions, and Safe Pagination

Find the source of duplicate rows

Suppose customers have many orders. Joining customers to orders produces one row per matching order, not one row per customer. Adding another one-to-many join, such as order lines, multiplies rows again. If a total unexpectedly grows after a join, verify each relationship's cardinality before adding DISTINCT; duplicate elimination can hide the modeling error and add cost.

Use EXISTS when the question is whether a related row exists rather than which related values to return:

SELECT c.customer_id, c.name
FROM customer c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.status = 'open'
);

Grouping and filtering

WHERE filters input rows before grouping. HAVING filters groups after aggregate values are computed. To find departments with at least five employees, group by department and use HAVING COUNT(*) >= 5. Be careful with nullable columns: COUNT(*) counts rows, while COUNT(column) excludes null values.

When joining multiple one-to-many tables, aggregate each side to the intended grain before joining if that avoids multiplying facts. State the grain of every intermediate result: one row per customer, per order, or per line item.

Window functions

Window functions keep input rows while calculating rank, lag, aggregate, or running values over a partition. ROW_NUMBER gives each row a unique sequence; RANK leaves gaps after ties; DENSE_RANK does not. Always define a deterministic tie-breaker if a specific row order matters.

SELECT department_id, employee_id, salary,
       DENSE_RANK() OVER (
         PARTITION BY department_id ORDER BY salary DESC
       ) AS salary_rank
FROM employee;

Filter a window result in an outer query or common table expression because window functions are evaluated after WHERE in the logical query processing order.

Pagination that does not drift

Offset pagination (OFFSET n FETCH/LIMIT m) is easy to understand but can become expensive at large offsets and can skip or repeat rows when data changes between requests. Include a unique ORDER BY, such as (created_at, id). For large mutable datasets, keyset pagination asks for rows after the last observed key and can use an index more efficiently.

Pagination and limiting syntax differs among PostgreSQL, MySQL, and Oracle versions. Keep query intent clear and test generated SQL against the actual target version rather than relying on one dialect's syntax everywhere.

Test query invariants

Create small fixtures containing duplicates, missing relationships, nulls, and tied sort keys. Assert both values and row counts. Then inspect the engine-specific execution plan for representative data sizes. Query correctness begins with choosing the right row grain; optimization follows after the result is right.