SQL Joins and Window Functions: A Practical Guide

SQL is full of tools that look intimidating at first but become second nature once you understand what problem they solve. Two of the most essential — and most misunderstood — are joins and window functions. In this guide, we'll break both down with real examples so you can use them confidently.

---

What Is a Join?

A join combines rows from two or more tables based on a related column. Instead of storing everything in one giant table, relational databases split data across tables — and joins are how you bring it back together.

Say you have an employees table and a departments table:

-- employees
| employee_id | first_name | department_id |
|-------------|------------|---------------|
| 1           | Alice      | 10            |
| 2           | Bob        | 20            |
| 3           | Carol      | NULL          |

-- departments
| department_id | department_name |
|---------------|-----------------|
| 10            | Engineering     |
| 20            | Marketing       |
| 30            | HR              |

Now let's look at the types of joins and what each one returns.

---

Types of Joins

INNER JOIN

Returns only rows that have a match in both tables.

SELECT e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

Result:

| first_name | department_name |
|------------|-----------------|
| Alice      | Engineering     |
| Bob        | Marketing       |

Carol is excluded — she has no department. Department 30 (HR) is also excluded — no employee belongs to it.

---

LEFT JOIN

Returns all rows from the left table, and matched rows from the right. Unmatched right-side values become NULL.

SELECT e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

Result:

| first_name | department_name |
|------------|-----------------|
| Alice      | Engineering     |
| Bob        | Marketing       |
| Carol      | NULL            |

Carol now appears, but her department is NULL.

---

RIGHT JOIN

The opposite — returns all rows from the right table, with matched rows from the left.

SELECT e.first_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

Result:

| first_name | department_name |
|------------|-----------------|
| Alice      | Engineering     |
| Bob        | Marketing       |
| NULL       | HR              |

HR appears even though it has no employees yet.

---

FULL OUTER JOIN

Returns all rows from both tables. Where there's no match, you get NULL on the missing side.

SELECT e.first_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;

Result:

| first_name | department_name |
|------------|-----------------|
| Alice      | Engineering     |
| Bob        | Marketing       |
| Carol      | NULL            |
| NULL       | HR              |

Note: MySQL doesn't support FULL OUTER JOIN natively. You can simulate it with a UNION of a LEFT JOIN and a RIGHT JOIN.

---

SELF JOIN

A table joined to itself. Useful for comparing rows within the same table — for example, finding employees and their managers when both are stored in the same table.

SELECT e.first_name AS employee, m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

---

When to Use Each Join

Join Type	Use When
INNER JOIN	You only want records with matches on both sides.
LEFT JOIN	You want all records from the left, matches optional.
RIGHT JOIN	You want all records from the right, matches optional.
FULL OUTER JOIN	You want everything, with or without a match.
SELF JOIN	Comparing rows within the same table.

---

What Are Window Functions?

Window functions perform calculations across a set of rows related to the current row — without collapsing those rows into a single result like GROUP BY would.

Think of it this way: GROUP BY gives you one row per group. A window function keeps all your rows intact but adds a calculated column based on a defined "window" of data.

The syntax looks like this:

function_name() OVER (
  PARTITION BY column
  ORDER BY column
  ROWS/RANGE BETWEEN ...
)

Let's look at the most useful ones.

---

Common Window Functions

ROW_NUMBER()

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

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

Result:

| first_name | department_id | salary | rank_in_dept |
|------------|---------------|--------|--------------|
| Alice      | 10            | 90000  | 1            |
| Dave       | 10            | 75000  | 2            |
| Bob        | 20            | 80000  | 1            |

Each department gets its own numbering, ordered by salary.

---

RANK() and DENSE_RANK()

Both assign rankings, but handle ties differently:

• RANK() skips numbers after a tie (1, 2, 2, 4)
• DENSE_RANK() doesn't skip (1, 2, 2, 3)

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

---

SUM() / AVG() as Window Functions

You can use aggregate functions as window functions to compute running totals or per-group averages without collapsing rows.

SELECT
  first_name,
  department_id,
  salary,
  SUM(salary) OVER (PARTITION BY department_id) AS dept_total,
  AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
FROM employees;

Every row keeps its data, but now has the department total and average alongside it — great for calculating what percentage of the department budget each employee represents.

---

LAG() and LEAD()

Access the value from a previous (LAG) or next (LEAD) row in the result set.

SELECT
  month,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
  revenue - LAG(revenue, 1) OVER (ORDER BY month) AS month_over_month_change
FROM monthly_sales;

This is incredibly useful for trend analysis — comparing this month's revenue to last month's without a self join.

---

NTILE()

Divides rows into a specified number of buckets.

SELECT
  first_name,
  salary,
  NTILE(4) OVER (ORDER BY salary DESC) AS salary_quartile
FROM employees;

Quartile 1 = top 25% earners. Great for segmenting data into equal groups.

---

Joins vs. Window Functions

They solve different problems, but you'll often use them together.

Aspect	Joins	Window Functions
Purpose	Combine data from multiple tables.	Calculate across rows in a single result set.
Output rows	Can increase or reduce row count.	Always keeps the same number of rows.
Aggregation	Use GROUP BY to aggregate.	Aggregates without collapsing rows.
Use Cases	Merging related tables, lookups.	Rankings, running totals, trend analysis.
Complexity	Grows with number of tables.	Grows with window frame definitions.

---

Putting It All Together

Here's a query that uses both — fetching each employee's name, department, salary, and how they rank within their department:

SELECT
  e.first_name,
  d.department_name,
  e.salary,
  RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS salary_rank,
  AVG(e.salary) OVER (PARTITION BY e.department_id) AS dept_avg_salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

In one query you get the joined department name, a salary ranking per department, and the department average — all without any extra subqueries or CTEs.

---

Key Takeaways

• Use INNER JOIN when you only care about matching rows on both sides.
• Use LEFT JOIN when the left table is your primary dataset and the right side is optional.
• Use window functions when you need calculations across rows without losing any rows.
• ROW_NUMBER, RANK, and DENSE_RANK are your go-to functions for ranking problems.
• LAG and LEAD make time-series comparisons clean and readable.
• You'll often combine joins and window functions in the same query — they complement each other well.

---