SQL Joins, Aggregations, and Window Functions: Query Patterns Cheatsheet
SQLDatabasesCheatsheetData AnalysisQuery Optimization

SQL Joins, Aggregations, and Window Functions: Query Patterns Cheatsheet

TThe Coding Club Editorial Team
2026-11-12
9 min read

A practical SQL cheatsheet for joins, aggregation, CTEs, window functions, duplicate rows, NULLs, and PostgreSQL, MySQL, and SQL Server differences.

SQL becomes much easier to reason about when joins, aggregation, common table expressions, and window functions are treated as separate query-building steps. This reference gives you reusable patterns, a small sample schema, dialect notes for PostgreSQL, MySQL, and SQL Server, and a debugging checklist for duplicate rows, NULL values, and incorrect totals.

Overview

Most difficult SQL bugs are not syntax errors. They are logic errors: a join multiplies rows, a filter removes unmatched records, or a grouped query answers a different question from the one you intended. A reliable workflow is to define the grain of each result, join only the rows you need, aggregate at the correct level, and use window functions when you need calculations without collapsing detail.

Sample schema

The examples below use four related tables:

customers (
  customer_id, customer_name, region
)

orders (
  order_id, customer_id, order_date, status, total_amount
)

order_items (
  order_item_id, order_id, product_id, quantity, unit_price
)

products (
  product_id, product_name, category
)

Before writing a query, state its intended grain in plain language. For example: “one row per customer,” “one row per order,” or “one row per order item.” This single decision helps you choose the correct join and prevents many accidental duplicates.

Join comparison

JoinReturnsTypical useRisk to check
INNER JOINRows with a match in both tablesOrders that have a known customerUnmatched rows disappear
LEFT JOINEvery row from the left table, plus matchesAll customers, including those with no ordersRight-side columns can be NULL
RIGHT JOINEvery row from the right table, plus matchesThe same logic as LEFT JOIN with table order reversedCan make query flow harder to read
FULL OUTER JOINMatched and unmatched rows from both tablesComparing two complete listsSupport and NULL handling vary by database

Prefer a LEFT JOIN when the business question starts with “show every…” For example, to show every customer and any matching orders:

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

Putting a condition on the right-hand table in the WHERE clause can unintentionally turn this into an effective INNER JOIN:

-- Unmatched customers are removed
WHERE o.status = 'completed'

If you want to preserve customers with no completed orders, put the condition in the join:

LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.status = 'completed';

Checklist by scenario

Scenario 1: Filter rows before joining

Use WHERE to filter the final row set, and use an ON condition to define which rows are eligible to match. For an INNER JOIN, these often produce equivalent results, but the distinction matters for OUTER JOINs.

SELECT o.order_id, o.order_date, c.customer_name
FROM orders AS o
INNER JOIN customers AS c
  ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
  AND o.order_date >= '2025-01-01';

For reusable filtering logic, begin with a CTE. A CTE names an intermediate result and makes a longer query easier to inspect:

WITH completed_orders AS (
  SELECT order_id, customer_id, order_date, total_amount
  FROM orders
  WHERE status = 'completed'
)
SELECT
  c.customer_name,
  co.order_id,
  co.total_amount
FROM completed_orders AS co
JOIN customers AS c
  ON c.customer_id = co.customer_id;

Scenario 2: Return one summary row per group

Use aggregate functions such as COUNT, SUM, AVG, MIN, and MAX with GROUP BY. Every selected column that is not aggregated should identify the grouping level.

SELECT
  c.region,
  COUNT(o.order_id) AS order_count,
  COALESCE(SUM(o.total_amount), 0) AS revenue
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.status = 'completed'
GROUP BY c.region;

Use COUNT(column) when you want to count non-NULL matches. Use COUNT(*) when you want to count rows produced by the join. With a LEFT JOIN, those are not always the same:

SELECT
  c.customer_id,
  COUNT(*) AS joined_rows,
  COUNT(o.order_id) AS actual_orders
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
GROUP BY c.customer_id;

COALESCE is useful when an aggregate over unmatched rows returns NULL and the report should display zero instead.

Scenario 3: Filter groups after aggregation

Use WHERE before grouping and HAVING after grouping. For example, this returns regions with at least five completed orders:

SELECT
  c.region,
  COUNT(o.order_id) AS completed_orders
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
GROUP BY c.region
HAVING COUNT(o.order_id) >= 5;

Scenario 4: Keep detail while calculating totals or rankings

Window functions calculate across related rows without reducing them to one row per group. The basic shape is:

function_name(...) OVER (
  PARTITION BY grouping_columns
  ORDER BY ordering_columns
)

To calculate each customer’s total while retaining every order:

SELECT
  o.order_id,
  o.customer_id,
  o.order_date,
  o.total_amount,
  SUM(o.total_amount) OVER (
    PARTITION BY o.customer_id
  ) AS customer_total
FROM orders AS o
WHERE o.status = 'completed';

To rank orders within each customer, use ROW_NUMBER, RANK, or DENSE_RANK:

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_sequence
FROM orders AS o;

The extra order_id sort makes ties deterministic when two orders share the same date. Use ROW_NUMBER when every row needs a unique position, RANK when tied values share a rank and leave gaps, and DENSE_RANK when tied values share a rank without gaps.

Scenario 5: Select the latest row per group

A common SQL query pattern is to rank rows in a CTE, then filter the ranking in an outer query:

WITH ranked_orders AS (
  SELECT
    o.*,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_date DESC, order_id DESC
    ) AS row_num
  FROM orders AS o
)
SELECT *
FROM ranked_orders
WHERE row_num = 1;

This pattern is generally clearer than relying on database-specific shortcuts. It also makes the tie-breaking rule visible.

Scenario 6: Calculate a running total

SELECT
  order_date,
  order_id,
  total_amount,
  SUM(total_amount) OVER (
    ORDER BY order_date, order_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_revenue
FROM orders
WHERE status = 'completed';

Specify a tie-breaker and, when supported by your target database, an explicit frame such as ROWS. This avoids ambiguity when multiple rows have the same ordering value.

What to double-check

Row multiplication

Joining one customer to many orders and then to many order items can produce multiple rows per order. If you sum an order-level amount after that join, the amount may be counted once for every item. Decide whether the calculation belongs at customer, order, or item grain, and aggregate before joining to a more detailed table when necessary.

WITH order_item_totals AS (
  SELECT
    order_id,
    SUM(quantity * unit_price) AS item_total
  FROM order_items
  GROUP BY order_id
)
SELECT
  o.order_id,
  o.total_amount,
  oit.item_total
FROM orders AS o
LEFT JOIN order_item_totals AS oit
  ON oit.order_id = o.order_id;

NULL values

NULL means “unknown” or “not present,” not zero and not an empty string. Comparisons such as column = NULL do not work as intended; use IS NULL or IS NOT NULL. Remember that arithmetic involving NULL usually produces NULL, so use COALESCE only when replacing the missing value with a defined fallback is logically correct.

Grouping rules

Do not add a column to GROUP BY merely to silence an error. Adding a column changes the grouping grain. Instead, decide whether the column should be aggregated, removed, or used to define a more detailed result.

Order of operations

A useful mental model is: FROM and joins create rows, WHERE filters rows, GROUP BY forms groups, aggregates calculate group values, HAVING filters groups, window functions calculate across the remaining rows, and ORDER BY sorts the final output. Database optimizers may execute an equivalent plan differently, but this logical order helps you diagnose query behavior.

Dialect differences

  • PostgreSQL: supports CTEs, window functions, and FULL OUTER JOIN. Date and interval expressions have PostgreSQL-specific forms, so verify syntax when moving queries.
  • MySQL: modern MySQL versions support CTEs and window functions, but a FULL OUTER JOIN is not available as a native join type. A UNION-based combination of LEFT and RIGHT JOINs may be needed, with care to avoid duplicate matches.
  • SQL Server: supports CTEs and window functions. A CTE must be preceded by a statement terminator when it follows another statement in the same batch; using ; consistently can prevent parsing surprises.

Check the documentation for the specific database version and compatibility mode used by your application. Differences can also affect date literals, identifier quoting, pagination, NULL ordering, and string concatenation.

Common mistakes

  • Using SELECT DISTINCT to hide duplicate rows: First find which join or relationship creates the extra rows. DISTINCT can hide a modeling or aggregation error and may remove genuinely different records.
  • Filtering a LEFT JOIN in WHERE: Move right-table conditions into the ON clause when unmatched left-side rows must remain.
  • Counting the wrong thing: Compare COUNT(*), COUNT(id), and COUNT(DISTINCT id) against the question being asked.
  • Joining before aggregating: Summarize a one-to-many table first when a later join would repeat values.
  • Using a window function where GROUP BY is required: Window functions retain detail; GROUP BY collapses rows. Choose based on the required output grain.
  • Relying on an incomplete ORDER BY: Add a stable tie-breaker when using ROW_NUMBER or selecting a latest record.
  • Assuming aliases work everywhere: A SELECT alias is not universally available in WHERE because WHERE is logically evaluated earlier. Use a CTE or derived table when you need to filter on a calculated alias.
  • Testing only with tidy data: Include customers without orders, duplicate dates, NULL values, multiple items per order, and groups with no qualifying rows in your test cases.

For a practical debugging workflow, reduce the query to its FROM and JOIN clauses, inspect row counts after each join, then add filters, grouping, and calculations one step at a time. A SQL formatter can improve readability, but formatting cannot correct an incorrect grain or join condition.

When to revisit

Return to this checklist whenever a report changes its level of detail, a new one-to-many relationship is added, or a query is adapted from one database engine to another. It is especially useful before recurring reporting or planning cycles, when production data may contain edge cases that a small development dataset did not expose.

Before shipping or refreshing a query, use this five-minute review:

  1. Write down the intended grain: one row per customer, order, item, or group.
  2. Run each join separately and compare row counts before and after it.
  3. Test at least one unmatched row, one NULL value, one duplicate ordering value, and one one-to-many relationship.
  4. Confirm whether filters belong in WHERE, ON, or HAVING.
  5. Compare a small hand-calculated example with the query result.
  6. Review the execution plan when the query runs against production-sized data, and index or rewrite only after confirming the actual bottleneck.
  7. Record the target database and version when syntax or behavior is dialect-specific.

Keep this reference alongside your other developer resources, such as the REST API testing guide when query results feed an API, or the Docker local development guide when reproducing database behavior locally. The goal is not to memorize every SQL clause. It is to make the result’s grain, relationships, and assumptions explicit before the query becomes part of a report or application.

Related Topics

#SQL#Databases#Cheatsheet#Data Analysis#Query Optimization
T

The Coding Club Editorial Team

Developer Resources Editor

Senior editor and content strategist. Writing about technology, design, and the future of digital media. Follow along for deep dives into the industry's moving parts.