SQL Joins and Window Functions: A Developer's Guide

Introduction

If you've ever worked with relational databases, you've almost certainly needed to combine data from multiple tables or perform calculations across related rows. That's exactly where Joins and Window Functions shine. These two SQL features are among the most powerful tools in a developer's toolkit — yet they're often misunderstood or underused.

In this article, we'll break them down clearly, write real queries, and make sure you walk away with a solid mental model of both.

---

Part 1: SQL Joins

What Are Joins?

A Join is SQL's way of combining rows from two or more tables based on a related column between them. Think of it like merging two spreadsheets based on a shared ID column.

Types of Joins

Join Type	What It Returns
INNER JOIN	Only rows that match in both tables
LEFT JOIN	All rows from the left table + matching rows from the right
RIGHT JOIN	All rows from the right table + matching rows from the left
FULL OUTER JOIN	All rows from both tables, with NULLs where there's no match
CROSS JOIN	Every combination of rows from both tables (Cartesian product)
SELF JOIN	A table joined with itself

Sample Tables

Let's use two simple tables throughout:

-- employees table
CREATE TABLE employees (
    emp_id    INT PRIMARY KEY,
    name      VARCHAR(100),
    dept_id   INT,
    salary    DECIMAL(10, 2)
);

-- departments table
CREATE TABLE departments (
    dept_id   INT PRIMARY KEY,
    dept_name VARCHAR(100)
);

Employees:

emp_id	name	dept_id	salary
1	Alice	10	70000
2	Bob	20	55000
3	Carol	10	80000
4	David	NULL	45000

Departments:

dept_id	dept_name
10	Engineering
20	Marketing
30	HR

INNER JOIN — The Most Common Join

Returns only employees who belong to a department that exists.

SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

Result:

name	dept_name
Alice	Engineering
Bob	Marketing
Carol	Engineering

David is excluded because he has no dept_id, and HR is excluded because no employee belongs to it.

LEFT JOIN — Include Everyone from the Left

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

Result:

name	dept_name
Alice	Engineering
Bob	Marketing
Carol	Engineering
David	NULL

David appears with a NULL department — the left table (employees) keeps all its rows.

FULL OUTER JOIN — Show Everything

SELECT e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id;

Result:

name	dept_name
Alice	Engineering
Bob	Marketing
Carol	Engineering
David	NULL
NULL	HR

HR appears even though no employee belongs to it.

SELF JOIN — Joining a Table with Itself

Useful for hierarchical data (e.g., employees and their managers).

-- Assume employees table has a manager_id column
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

---

Part 2: Window Functions

What Are Window Functions?

A Window Function performs a calculation across a set of rows that are somehow related to the current row — without collapsing those rows into a single result (unlike GROUP BY).

Think of it like this: GROUP BY squishes rows together. Window functions look across rows while keeping each row intact.

The basic syntax is:

function_name() OVER (
    PARTITION BY column    -- divide into groups
    ORDER BY column        -- sort within each group
    ROWS/RANGE clause      -- define the "frame"
)

Common Window Functions

Function	Purpose
ROW_NUMBER()	Assigns a unique row number per partition
RANK()	Ranks rows; ties get the same rank, next rank skips
DENSE_RANK()	Like RANK, but no gaps after ties
NTILE(n)	Divides rows into n roughly equal buckets
LAG()	Access a previous row's value
LEAD()	Access a next row's value
SUM() OVER	Running/cumulative sum
AVG() OVER	Moving average
FIRST_VALUE()	First value in the window
LAST_VALUE()	Last value in the window

ROW_NUMBER() — Rank Within a Group

SELECT
    name,
    dept_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank_in_dept
FROM employees;

Result:

name	dept_id	salary	rank_in_dept
Carol	10	80000	1
Alice	10	70000	2
Bob	20	55000	1

Each department gets its own ranking. Carol is #1 in Engineering, Bob is #1 in Marketing.

RANK() vs DENSE_RANK() — Handling Ties

SELECT
    name,
    salary,
    RANK()       OVER (ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

Result (with a tie at 70000):

name	salary	rank	dense_rank
Carol	80000	1	1
Alice	70000	2	2
Bob	70000	2	2
David	45000	4	3

RANK() skips 3 after the tie. DENSE_RANK() doesn't skip — it goes straight to 3.

Running Total with SUM() OVER

SELECT
    name,
    salary,
    SUM(salary) OVER (ORDER BY emp_id) AS running_total
FROM employees;

Result:

name	salary	running_total
Alice	70000	70000
Bob	55000	125000
Carol	80000	205000
David	45000	250000

Each row shows the cumulative salary total up to that point.

LAG() and LEAD() — Compare Row to Its Neighbor

SELECT
    name,
    salary,
    LAG(salary)  OVER (ORDER BY salary) AS prev_salary,
    LEAD(salary) OVER (ORDER BY salary) AS next_salary
FROM employees;

Result:

name	salary	prev_salary	next_salary
David	45000	NULL	55000
Bob	55000	45000	70000
Alice	70000	55000	80000
Carol	80000	70000	NULL

Great for comparing month-over-month changes or spotting trends.

Combining Joins + Window Functions

This is where things get really powerful. Let's find each employee's salary rank within their department name (not just ID):

SELECT
    e.name,
    d.dept_name,
    e.salary,
    RANK() OVER (PARTITION BY d.dept_name ORDER BY e.salary DESC) AS dept_salary_rank
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

Result:

name	dept_name	salary	dept_salary_rank
Carol	Engineering	80000	1
Alice	Engineering	70000	2
Bob	Marketing	55000	1

---

Key Takeaways

1. INNER JOIN is your default — use it when you only want rows that match in both tables.
2. LEFT JOIN is great when you want to keep all records from your primary (left) table regardless of matches.
3. FULL OUTER JOIN is ideal for data audits or finding unmatched records on either side.
4. Window Functions don't reduce your rows — they add a computed column while keeping the full dataset visible.
5. Use ROW_NUMBER() to pick one row per group (top N queries), RANK() for leaderboards, and LAG/LEAD() for time-series or sequential comparisons.
6. Combining Joins and Window Functions gives you the ability to do complex analytics that would otherwise require multiple subqueries or CTEs.

---

Final Thoughts

Joins and Window Functions are two pillars of advanced SQL. Joins bring your data together; window functions help you analyze it with context. Once you're comfortable using both — especially together — you'll find yourself writing cleaner, faster, and more expressive queries.

Practice with real datasets, experiment with edge cases (like NULLs in join keys), and you'll build an intuition that no amount of reading alone can give you.

Happy querying!