Joins and Window Functions Made Super Simple

Joins and window functions are two powerful tools in SQL that help you combine data and do smart calculations.

Joins let you bring together information from two or more tables.
Window functions let you add extra information like averages, ranks, or “previous” values without losing any rows.

How they work with simple, everyday examples.

Joins: Stitching Tables Together
Imagine you have two paper lists:

Employees list

Columns: name, salary, department_id
Departments list
Columns: id, name

We want to see each employee’s name and salary plus their department name. Since that information is split between two lists, we use a join to connect them using the department_id.

Example 1.
Basic join (employees + departments)
sql
SELECT employees.name,
departments.name AS department_name,
employees.salary
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;
This returns only employees who have a matching department.
For each employee, the department name is pulled from the second table.

Example 2.
Left join (keep all employees)
sql
SELECT employees.name,
departments.name AS department_name,
employees.salary
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id;
Every employee appears in the result.
If there is no matching department, department_name will be blank (NULL).

Window Functions: Adding Helper Columns
A window function is like adding helper columns that show extra information “around” each row, such as:

“What is the average salary in this department?”
“How high is this employee’s salary compared to others in the same department?”

The beauty of a window function is that it keeps every original row and just adds this extra information.

Example 3.
Show average salary per department beside each employee
sql
SELECT employees.name,
departments.name AS department_name,
employees.salary,
AVG(employees.salary) OVER (PARTITION BY departments.id) AS avg_dept_salary
FROM employees
JOIN departments
ON employees.department_id = departments.id;
AVG(employees.salary) calculates the average salary.

OVER (PARTITION BY departments.id) means: “for each department, compute this average.”

Every employee row still appears, and next to their name you see the average salary for their department.

Example 4.
Rank employees by salary within each department
sql
SELECT employees.name,
departments.name AS department_name,
employees.salary,
ROW_NUMBER() OVER (PARTITION BY departments.id ORDER BY employees.salary DESC) AS row_number,
RANK() OVER (PARTITION BY departments.id ORDER BY employees.salary DESC) AS rank_value,
DENSE_RANK() OVER (PARTITION BY departments.id ORDER BY employees.salary DESC) AS dense_rank_value
FROM employees
JOIN departments
ON employees.department_id = departments.id;
PARTITION BY departments.id: do the ranking separately for each department.

ORDER BY employees.salary DESC: sort from highest to lowest salary.

ROW_NUMBER(): gives each row a unique number (1, 2, 3, ...) even if salaries are the same.

RANK(): ties get the same rank, then it skips the next number (1, 1, 3).

DENSE_RANK(): ties get the same rank, but no gaps (1, 1, 2).

Key Points Summary
Joins are like stitching two lists together using a shared ID (like department ID).

• INNER JOIN: only show rows that match in both lists.
• LEFT JOIN: keep all rows from the first list, even if there is no match in the second.

Window functions are like adding helper columns that calculate things per group without losing any rows.

You can compute averages, sums, or rankings for each group while still seeing every individual row.

The OVER (PARTITION BY ... ORDER BY ...) tells SQL: “do this calculation for each group, in this order.”

There are three main families of window functions:

• Aggregate window functions like SUM, AVG, COUNT, MIN, MAX.
• Ranking window functions like ROW_NUMBER, RANK, DENSE_RANK, NTILE.
• Value/offset window functions like LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE (you can learn these later).

The Concepts
Joins are like taking two separate lists and combining them using a shared ID.

• INNER JOIN is like “only show people who appear in both lists.”
• LEFT JOIN is like “show everyone from the first list and add matching info from the second where possible.”
• Window functions are like putting sticky notes on each row that say things like “the average salary in this department” or “this employee is number 3 by salary here.” They don’t hide or remove anything; they just add extra information next to each row.
• OVER (PARTITION BY ... ORDER BY ...) is how you tell SQL which group to use and in what order.

Simple Analogy
Think of:

• Joins as merging two spreadsheets by a common column (like employee ID).
• Window functions as adding extra columns in that merged sheet that show things like “average per group”, “ranking per group”, or “value from the row above”.