Mastering SQL Joins and Window Functions: A Comprehensive Guide

SQL is the backbone of data manipulation in relational databases, and two of its most essential features are joins and window functions. Joins allow you to combine data from multiple tables, while window functions enable advanced calculations across rows without collapsing them into aggregates. Whether you're a beginner or an experienced data analyst, understanding these concepts can significantly enhance your querying skills. In this article, we'll dive deep into both, with explanations, examples, and tips.

What Are SQL Joins?
Joins are used to retrieve data from two or more tables based on a related column between them.

Let's imagine a small e-commerce database with two tables:

-- customers
+----+---------+
| id | name    |
+----+---------+
| 1  | Aisha   |
| 2  | Brian   |
| 3  | Carla   |
+----+---------+

-- orders
+----+-------------+--------+
| id | customer_id | amount |
+----+-------------+--------+
| 1  | 1           | 250    |
| 2  | 1           | 100    |
| 3  | 2           | 400    |
+----+-------------+--------+

Notice Carla (id 3) has no orders yet. Keep that in mind: it matters a lot once we get to joins.

Part 1: SQL Joins

A join combines rows from two or more tables based on a related column, in our case customer_id.

INNER JOIN

Returns only rows that match in both tables.

SELECT customers.name, orders.amount
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

Result:

Aisha | 250
Aisha | 100
Brian | 400

Carla disappears entirely because she has no matching order. This is the most common join, but it silently drops unmatched rows, a frequent source of "wait, why is my data missing?" bugs.

LEFT JOIN

Returns all rows from the left table, plus matches from the right table. Unmatched rows get NULL.

SELECT customers.name, orders.amount
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

Result:

Aisha | 250
Aisha | 100
Brian | 400
Carla | NULL

Now Carla shows up, with NULL for amount. Use LEFT JOIN whenever you need to keep every record from your "main" table, matched data or not, e.g. "show me all customers, even ones who haven't ordered."

RIGHT JOIN

The mirror image of LEFT JOIN, it keeps all rows from the right table instead. It's less commonly used in practice; most people just flip the table order and use LEFT JOIN instead, since it's easier to read.

FULL OUTER JOIN (MySQL workaround)

MySQL doesn't support FULL OUTER JOIN natively. You simulate it with UNION:

SELECT customers.name, orders.amount
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
UNION
SELECT customers.name, orders.amount
FROM customers
RIGHT JOIN orders ON customers.id = orders.customer_id;

This gives you every row from both tables, matched where possible.

Quick rule of thumb: if you're not sure which join to use, ask "do I want to keep unmatched rows, and from which table?" That answer picks your join for you.

Part 2: Window Functions

Joins combine tables. Window functions let you do calculations across a set of rows, like running totals or rankings, without collapsing them into a single row (which is what GROUP BY does).

The basic shape:

SOME_FUNCTION() OVER (PARTITION BY column ORDER BY column)

• PARTITION BY splits your data into groups (like GROUP BY, but without merging rows).
• ORDER BY decides the order within each group, which matters for ranking and running totals.

ROW_NUMBER()

Assigns a unique, sequential number to each row within a partition.

SELECT
  customer_id,
  amount,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS row_num
FROM orders;

This numbers each customer's orders from biggest to smallest, handy for finding "each customer's top order."

RANK() and DENSE_RANK()

Similar to ROW_NUMBER(), but they handle ties differently:

• RANK(): ties share a rank, but leaves a gap afterward (1, 1, 3).
• DENSE_RANK(): ties share a rank, no gap (1, 1, 2).

SELECT
  customer_id,
  amount,
  RANK() OVER (ORDER BY amount DESC) AS overall_rank
FROM orders;

Running Totals with SUM()

SELECT
  customer_id,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY id) AS running_total
FROM orders;

For Aisha, this gives 250, then 350 (250+100), a running total per customer, without needing a self-join or subquery.

Comparing Rows with LAG() and LEAD()

LAG() looks at the previous row; LEAD() looks at the next row, great for comparing an order to the one before it.

SELECT
  customer_id,
  amount,
  LAG(amount) OVER (PARTITION BY customer_id ORDER BY id) AS previous_amount
FROM orders;

Joins vs. Window Functions: When to Use Which

Use case	Tool
Combining data from two+ tables	JOIN
Keeping all rows from one table regardless of matches	LEFT JOIN
Ranking rows within groups	ROW_NUMBER() / RANK()
Running totals, moving averages	Window function with SUM()/AVG()
Comparing a row to the previous/next one	LAG() / LEAD()
Collapsing rows into one summary row per group	GROUP BY (not a window function)

The key mental model: joins add columns from other tables; window functions add calculations across rows you already have, without losing any of them.

Wrapping Up

Start by getting comfortable with INNER JOIN and LEFT JOIN, since they cover the vast majority of real-world queries. Once those feel natural, window functions like ROW_NUMBER() and running SUM() will feel like a natural next step rather than dark magic.

Try rewriting a GROUP BY query you've written before using a window function instead. It's one of the fastest ways to make the concept click.

Happy querying! 🚀