SQL Intermediate Recap — JOIN, Subqueries, and Window Functions at a Glance

A single-page recap of all 14 intermediate SQL articles — aggregate functions and GROUP BY, INNER/OUTER JOIN, subqueries and EXISTS, set operators, CTEs, window functions, and transactions — tied together with diagrams and a classification table.

Big picture of what you learned in SQL Intermediate

This page consolidates the syntax covered in the SQL Intermediate series along four axes: aggregating multiple rows into one, joining multiple tables with JOIN and subqueries, computing across rows with window functions, and committing changes together with transactions.

Use the links to jump back to any article for review.

Big picture of what SQL Intermediate covers

Roll rows up with aggregates, combine multiple tables with JOIN and subqueries, compute across rows with window functions, and commit your changes together with transactions.

Aggregate rows and join multiple tables

Aggregate functions and GROUP BY collapse multiple rows into one value, while JOIN connects multiple tables through a shared column.

The diagram below maps out the 2 aggregate articles and the 3 join articles.

5 articles on aggregation and joining

Aggregate functions roll rows up, and GROUP BY aggregates per group. JOIN spans 3 articles — INNER, OUTER, and practical usage — covering how to connect tables.

Category	Article	What you'll learn
Aggregate	Aggregate Functions	Whole-table aggregation with COUNT / SUM / AVG / MIN / MAX
Aggregate	GROUP BY and HAVING	Group aggregation, HAVING, and the difference vs. WHERE
Join	INNER JOIN and aliases	Aliases, INNER JOIN, ON / USING
Join	OUTER / CROSS / self join	LEFT / RIGHT / FULL, CROSS, self joins
Join	JOIN in practice	Combining JOIN with WHERE / ORDER BY / CASE

Compose with subqueries, set operators, and window functions

Subqueries and EXISTS let you embed one query inside another, set operators and CTEs glue result sets together and keep things readable, and window functions compute rank and running totals across rows.

The diagrams below map subqueries / EXISTS / set operators / CTE, and the 3 window function articles.

5 articles on subqueries, set operators, and CTEs

Subqueries and EXISTS embed one query inside another; set operators and CTEs glue result sets together to keep complex SQL readable.

4 articles on window functions and transactions

Window functions span 3 articles — OVER / PARTITION, ORDER BY and frames, and ranking / analytic functions. Transactions group changes so you can commit or undo them as one.

Category	Article	What you'll learn
Subquery	Subquery ① IN / WHERE	Scalar, IN, aggregation in WHERE, intro to correlated subqueries
Subquery	Subquery ② FROM / SELECT	Derived tables, SELECT-clause subqueries, CREATE / INSERT SELECT
EXISTS	EXISTS and correlated subqueries	EXISTS / NOT EXISTS and how NULL interacts with EXISTS
Set ops	UNION / INTERSECT / EXCEPT	Union, intersection, difference, and UNION ALL
CTE	WITH (CTE) and recursive CTEs	Readability gains and hierarchy traversal with recursive CTEs
Window ①	OVER / PARTITION BY	Aggregate windows and PARTITION BY
Window ②	ORDER BY and frames	Running totals and frame specs (ROWS / RANGE)
Window ③	Ranking / analytic functions and execution order	ROW_NUMBER / RANK / LAG / LEAD / NTILE and SQL execution order
Transaction	Transactions and locks	BEGIN / COMMIT / ROLLBACK, SAVEPOINT, AUTOCOMMIT

Nice work!

You've now tied together aggregation and GROUP BY, joining multiple tables with JOIN, subqueries and EXISTS, set operators and CTEs, window functions, and transactions in one sweep.

Combine these pieces and you can write the vast majority of the analysis, aggregation, and reporting queries you'll bump into in real work.

Jump back to any article that caught your eye and try the patterns on your own data.

Back to SQL Intermediate