SQL Joins, Aggregations, and Window Functions: A Practical Query Reference

Overview

Most reporting queries answer one of three questions: how do these rows relate, how should several rows be summarized, or how should each row be compared with other rows? SQL has a different tool for each job.

• Joins combine columns from related tables.
• GROUP BY reduces many rows into one row per group.
• HAVING filters groups after aggregation.
• Subqueries place one query inside another when a result is needed as an input.
• Common table expressions (CTEs) name query steps to make multi-stage logic easier to read.
• Window functions calculate across related rows without collapsing the original rows.

Assume the following small schema throughout this SQL joins cheat sheet:

customers(id, name, region)
orders(id, customer_id, order_date, status, total_amount)
order_items(id, order_id, product_id, quantity, unit_price)
products(id, name, category)

The most important design question is whether the output should contain one row per customer, one row per order, one row per product, or every original detail row. Decide that grain before writing the query. Unexpected totals and duplicate rows usually begin when the intended grain is unclear.

SQL join types at a glance

For example, this returns only customers with at least one order:

SELECT c.id, c.name, o.id AS order_id, o.total_amount
FROM customers AS c
INNER JOIN orders AS o
  ON o.customer_id = c.id;

To retain customers with no orders, change the join type:

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

Notice that a customer with three orders appears three times. That is correct for an order-level result, but not for a customer-level report. Aggregate after the join if the desired result is one row per customer.

Maintenance cycle

A reference article remains useful when its examples are checked against the way queries are actually written. Review this guide on a regular schedule, and also whenever your database engine, schema, or reporting requirements change.

GROUP BY and HAVING patterns

Use GROUP BY to define the output grain and aggregate functions to calculate values for each group:

SELECT customer_id,
       COUNT(*) AS order_count,
       SUM(total_amount) AS revenue,
       AVG(total_amount) AS average_order_value
FROM orders
WHERE status = 'completed'
GROUP BY customer_id;

WHERE filters individual rows before grouping. HAVING filters groups after the aggregates have been calculated:

SELECT customer_id,
       COUNT(*) AS order_count,
       SUM(total_amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING SUM(total_amount) > 1000;

A useful rule is: put ordinary row conditions in WHERE, and conditions involving COUNT, SUM, or another aggregate in HAVING. SQL dialects differ in how permissive they are about selecting columns that are not listed in GROUP BY; writing explicit grouping is the more portable approach.

Subqueries and CTEs

A subquery is suitable when a result is small and used once. This example finds orders above the overall average:

SELECT id, customer_id, total_amount
FROM orders
WHERE total_amount > (
  SELECT AVG(total_amount)
  FROM orders
);

Use a CTE when the query has logical stages or when a derived result will be joined or referenced more clearly by name:

WITH customer_totals AS (
  SELECT customer_id,
         SUM(total_amount) AS revenue
  FROM orders
  WHERE status = 'completed'
  GROUP BY customer_id
)
SELECT c.name, ct.revenue
FROM customer_totals AS ct
JOIN customers AS c
  ON c.id = ct.customer_id
WHERE ct.revenue > 1000
ORDER BY ct.revenue DESC;

CTEs improve readability, but they are not automatically faster than an equivalent subquery. For performance-sensitive work, inspect the execution plan supported by your database and test with representative data.

Window functions examples

Window functions calculate across a set of related rows while preserving each input row. The PARTITION BY clause defines the comparison group, and ORDER BY defines sequence within that group.

SELECT id,
       customer_id,
       order_date,
       total_amount,
       ROW_NUMBER() OVER (
         PARTITION BY customer_id
         ORDER BY order_date DESC, id DESC
       ) AS recent_order_number
FROM orders;

This labels each customer's newest order as number one. To return only that newest order, wrap the window calculation in a CTE:

WITH numbered_orders AS (
  SELECT o.*,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY order_date DESC, id DESC
         ) AS row_number
  FROM orders AS o
)
SELECT *
FROM numbered_orders
WHERE row_number = 1;

Other common window patterns include running totals and comparisons with the previous row:

SELECT customer_id,
       order_date,
       total_amount,
       SUM(total_amount) OVER (
         PARTITION BY customer_id
         ORDER BY order_date, id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_customer_total,
       LAG(total_amount) OVER (
         PARTITION BY customer_id
         ORDER BY order_date, id
       ) AS previous_order_amount
FROM orders;

RANK(), DENSE_RANK(), and ROW_NUMBER() are not interchangeable. ROW_NUMBER() always assigns a distinct sequence, while ranking functions can assign the same rank when values tie. Add a stable tie-breaker such as an ID when repeatable results matter.

Signals that require updates

Refresh this reference when any of the following signals appear:

• A database engine introduces, removes, or changes support for a function, join type, CTE behavior, or window-frame syntax.
• Readers repeatedly ask why a query works in PostgreSQL but fails in MySQL, SQL Server, or SQLite.
• Search intent shifts from basic joins toward topics such as deduplication, date-based reporting, recursive CTEs, or query performance.
• An example uses ambiguous dates, null handling, aliases, or ordering and produces different results across engines.
• A schema example hides a one-to-many relationship that could lead readers to double-count revenue.

Dialect notes should be added carefully. Core clauses such as JOIN, GROUP BY, and many window functions are widely available, but date functions, identifier quoting, pagination, string aggregation, and null-handling functions can differ. Mark engine-specific examples explicitly instead of presenting one dialect as universal.

Common issues

Duplicate rows after a join

A one-to-many join multiplies the parent row. If a customer has five orders and each order has four items, joining all three tables can produce twenty detail rows for that customer. Do not fix this with DISTINCT until you understand the relationship. First decide whether the report needs customer, order, or item grain; then aggregate at the correct stage.

Incorrect totals

Summing an order total after joining it to its items can count the same order total once per item. Either aggregate items into one row per order before joining, or sum item-level values instead:

SELECT order_id,
       SUM(quantity * unit_price) AS calculated_order_total
FROM order_items
GROUP BY order_id;

Filtering away LEFT JOIN results

This query appears to request all customers, but the WHERE condition removes customers without matching orders:

SELECT c.name, o.status
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
WHERE o.status = 'completed';

If the condition belongs to the optional match, place it in the join condition:

SELECT c.name, o.status
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
 AND o.status = 'completed';

Nulls and empty groups

Aggregates usually ignore null values, while COUNT(*) counts rows. Use COUNT(column) when you want to count only non-null values, and use COALESCE when a report should display zero instead of null:

SELECT c.id,
       c.name,
       COALESCE(SUM(o.total_amount), 0) AS revenue
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
GROUP BY c.id, c.name;

Unstable top-row queries

A window query that orders only by a non-unique date can return different tied rows. Add a unique secondary key, and make the ordering rule part of the query rather than relying on storage order. SQL tables have no guaranteed natural order without an explicit ORDER BY.

When to revisit

Use this reference as a working checklist rather than a one-time tutorial. Revisit it on a scheduled review cycle—monthly or quarterly is reasonable for an actively maintained team reference—and immediately when a query starts returning unexpected totals, a database version changes, or a report gains a new requirement.

1. Write down the desired grain. State whether the output is one row per customer, order, item, date, or another entity.
2. Map the relationships. Identify primary keys, foreign keys, and every one-to-many join before selecting columns.
3. Choose the smallest suitable pattern. Use a join for related columns, aggregation for summaries, a CTE for readable stages, and a window function when rows must remain visible.
4. Test edge cases. Include customers with no orders, duplicate dates, null values, tied rankings, and orders with multiple items.
5. Compare the result with a manual check. Validate a few groups by hand or with a simpler query before trusting a complex report.
6. Check the target dialect. Confirm date syntax, null functions, window support, identifier rules, and execution-plan behavior in the database you actually run.

Keep a small set of verified queries beside your schema documentation, and update the examples when the schema or reporting definitions change. For related developer references, see the developer tool guide for JSON, SQL, regex, JWT, and encoding utilities and the developer cheatsheet for JSON, regex, SQL, URLs, and Base64. A precise, tested query pattern is more useful than a large collection of snippets whose output grain is unclear.