SQL patterns are easier to choose when the data task is stated clearly. This cookbook provides copyable examples for JOINs, aggregates, common table expressions, window functions, deduplication, pagination, NULL handling, and date filtering, while pointing out the places where database dialects and query plans matter.
Overview
Most SQL problems are variations of a small set of operations: combine related rows, summarize values, compare a row with nearby rows, select the latest record, or restrict results to a time range. Starting with the right pattern helps you avoid accidental duplicates, incorrect totals, and filters that prevent useful indexes from being used.
The examples below use broadly familiar SQL syntax and assume tables such as customers, orders, and order_items. Adapt table and column names to your schema, then verify the result with a small test case before using the query in a report, API, migration, or production job.
Quick pattern reference
| Data task | Useful pattern | Primary caution |
|---|---|---|
| Keep only matching rows | INNER JOIN | Unmatched rows are removed |
| Keep every row from the first table | LEFT JOIN | Right-side columns may be NULL |
| Calculate one result per group | GROUP BY | Nonaggregated selected columns must be valid for the dialect |
| Rank or compare rows without collapsing them | Window function | Define a deterministic ordering |
| Build a readable multi-step query | CTE | Check execution plans for expensive or repeated work |
| Filter a time interval | Half-open range | Use the correct time zone and boundary |
For formatting and review, a SQL formatter can make a long query easier to read and troubleshoot. Formatting does not change the logic, so still inspect joins, filters, and grouping manually.
JOINs: combine related data
Use an inner join when a result should contain only rows with a match in both tables:
SELECT
o.order_id,
o.order_date,
c.customer_id,
c.email
FROM orders AS o
INNER JOIN customers AS c
ON c.customer_id = o.customer_id;Use a left join when the first table is the complete list you want to preserve. For example, this finds customers who have not placed an order:
SELECT
c.customer_id,
c.email
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;A frequent error is placing a right-table filter in the WHERE clause when the intention is to preserve unmatched rows. Compare these two forms:
-- Preserves customers with no recent order
SELECT c.customer_id, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
AND o.order_date >= :start_date;Putting o.order_date >= :start_date in WHERE would reject rows where o.order_date is NULL, effectively turning much of the left join into an inner join.
Aggregates: summarize without accidental inflation
Grouped aggregates return one row per grouping key:
SELECT
o.customer_id,
COUNT(*) AS order_count,
SUM(o.total_amount) AS lifetime_value
FROM orders AS o
GROUP BY o.customer_id;When joining a parent table to a one-to-many child table, calculate what you need at the correct grain. Joining orders to order items and then summing an order-level total can multiply that total once for every item. A safer approach is to aggregate items first:
WITH item_totals AS (
SELECT
order_id,
SUM(quantity * unit_price) AS item_subtotal
FROM order_items
GROUP BY order_id
)
SELECT
o.order_id,
o.customer_id,
i.item_subtotal
FROM orders AS o
LEFT JOIN item_totals AS i
ON i.order_id = o.order_id;Before adding DISTINCT to make duplicate-looking rows disappear, identify why the duplicates exist. They may represent valid child records rather than duplicate data.
Maintenance cycle
SQL examples remain useful when their assumptions are visible. Review this cookbook on a scheduled cycle, and review any copied query when its schema, database engine, or reporting requirement changes. A practical maintenance routine has four steps.
- Confirm the data grain. Write down whether one row represents a customer, order, item, event, or another entity. Every join and aggregate should be checked against that grain.
- Test with boundary cases. Include a parent with no children, multiple children, a
NULLvalue, a duplicate timestamp, and records exactly on the date boundaries. - Check the execution plan. Look for unexpectedly large scans, repeated work, costly sorts, and joins that produce far more rows than expected. The exact plan commands differ between database systems.
- Record dialect assumptions. Note whether the query depends on a particular date function, identifier quoting style, pagination feature, or null-handling function.
Common table expressions make multi-step logic easier to review:
WITH monthly_orders AS (
SELECT
customer_id,
DATE_TRUNC('month', order_date) AS order_month,
COUNT(*) AS order_count
FROM orders
GROUP BY customer_id, DATE_TRUNC('month', order_date)
)
SELECT *
FROM monthly_orders
WHERE order_count > 3;DATE_TRUNC is not universal syntax; some systems use different functions for truncating dates. Treat this query as a pattern and confirm the equivalent function in your database.
Signals that require updates
Update a query or reference example when one of these signals appears:
- The schema changes: a column is renamed, a relationship becomes many-to-many, or a timestamp changes from local time to UTC.
- Results become suspicious: totals rise after a new join, row counts change unexpectedly, or a supposedly unique result contains duplicates.
- Performance changes: a query that was acceptable begins timing out after data growth, a new filter is added, or an index is removed.
- The database dialect changes: a team migrates between database engines or introduces a compatibility layer.
- Search intent changes: readers increasingly need examples for a different engine, newer pagination methods, or a specific reporting problem.
Window functions: compare rows without grouping them away
Window functions calculate across related rows while retaining each row in the result. To rank orders for each customer:
SELECT
o.order_id,
o.customer_id,
o.order_date,
o.total_amount,
ROW_NUMBER() OVER (
PARTITION BY o.customer_id
ORDER BY o.order_date DESC, o.order_id DESC
) AS order_rank
FROM orders AS o;The second ordering column makes ties deterministic when two orders share a timestamp. To return only the latest order per customer, place the window query in a CTE:
WITH ranked_orders AS (
SELECT
o.*,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC, order_id DESC
) AS row_number
FROM orders AS o
)
SELECT *
FROM ranked_orders
WHERE row_number = 1;For running totals, use an ordered window:
SELECT
customer_id,
order_date,
total_amount,
SUM(total_amount) OVER (
PARTITION BY customer_id
ORDER BY order_date, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders;Pagination and deduplication
Offset pagination is simple and widely supported:
SELECT order_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC, order_id DESC
LIMIT :page_size OFFSET :offset;Always use a stable, unique tie-breaker in the ORDER BY. For large or changing datasets, keyset pagination can avoid repeatedly skipping earlier rows:
SELECT order_id, order_date, total_amount
FROM orders
WHERE (order_date, order_id) < (:last_date, :last_id)
ORDER BY order_date DESC, order_id DESC
LIMIT :page_size;Row-value comparison syntax varies, so the equivalent expanded condition may be required by your database. Test pagination while records are being inserted to confirm that the API does not miss or repeat rows.
Common issues
Date filtering and time zones
For a day or month, a half-open interval is often easier to reason about than an inclusive end timestamp:
SELECT order_id, order_date
FROM orders
WHERE order_date >= :period_start
AND order_date < :next_period_start;For example, a monthly report can use the first instant of the target month and the first instant of the following month. This avoids guessing the final fractional second of the period. Define whether stored timestamps are UTC and convert input boundaries consistently. Database-specific functions for constructing dates and converting time zones should be documented beside the query.
NULL handling
NULL means unknown or missing, not zero or an empty string. Use IS NULL and IS NOT NULL for checks:
SELECT customer_id, email
FROM customers
WHERE phone_number IS NULL;Use a null-handling function when a fallback is appropriate:
SELECT
customer_id,
COALESCE(preferred_name, legal_name, 'Unknown') AS display_name
FROM customers;Be careful with aggregates: COUNT(*) counts rows, while COUNT(column_name) generally excludes rows where that column is NULL. A left join followed by COUNT(*) can count the preserved parent row even when no child exists; counting a non-null child key is often the intended choice.
Parameterization and safety
Use parameters for values supplied by an application instead of concatenating user input into SQL text. Parameters also make the intended boundary values easier to test. Do not treat a formatter, preview tool, or decoder as a security boundary, and avoid pasting sensitive production data into third-party tools. For authentication-related data, the JWT decoder guide explains why reading a token is different from validating its signature and claims.
When to revisit
Use this cookbook as a starting reference, then revisit the relevant pattern whenever a query is copied into a new system. Schedule a broader review at a sensible interval for your project, especially after a database upgrade, schema migration, reporting redesign, or significant data-volume change. Also return to the examples when readers or teammates report that an engine-specific detail is unclear.
A practical review checklist:
- State the expected result grain in a comment or query note.
- Check every join key and confirm whether the relationship is one-to-one, one-to-many, or many-to-many.
- Test no-match, duplicate,
NULL, boundary-date, and tie-ordering cases. - Compare the actual row count before and after each major join.
- Inspect the execution plan for production-sized data.
- Confirm date, time-zone, pagination, and function syntax for the target SQL dialect.
- Format the final query and keep the readable version under version control.
For broader backend work, pair query review with deployment and API checks. The GitHub Actions deployment checklist can help organize release verification, while the CORS troubleshooting guide covers a separate but common API integration failure. Keeping these references current turns a one-time SQL example into a dependable working resource.