A Simple Guide to JOINs and Window Functions

1. Joins

Think of joins as putting puzzles together. They combine tables. Let's say you want information from two tables that belong to the same person or thing, then JOINs relates this information. For instance:

Most Common JOIN types

1. INNER JOIN - Only matching records from both tables.
   In the example in the table above, INNER JOIN returns 3 rows: only people who have a department.

2. LEFT JOIN - All from the left table + matching from the right.
   In the example in the table above, LEFT JOIN returns 3 rows: all people, even those without a department.

3. RIGHT JOIN - All from the right table + matching from the left.
   In the example in the table above, RIGHT JOIN returns 4 rows: all departments, even those without people.

4. FULL OUTER - All records from both. In the example in the table above, it returns 4 rows: everyone and every department.

Sample Queries:

 -- INNER JOIN (most common)
SELECT employees.name, departments.dept_name
FROM employees 
INNER JOIN departments ON employees.dept_id = departments.dept_id;
-- Result: Alice→Sales, Bob→Marketing, Charlie→Sales

-- LEFT JOIN (very popular for "show all + extra info if exists")
SELECT employees.name, departments.dept_name
FROM employees
LEFT JOIN departments ON employees.dept_id = departments.dept_id;
-- Result: Alice→Sales, Bob→Marketing, Charlie→Sales (no missing people)

2. Window Functions

These are calculations across rows without grouping. The normal GROUP BY collapses rows, but window functions let you see the group result while still keeping every row.

Think of it as: for each row, tell me something about its group.

Sample Queries:

-- Running total
SELECT sale_date, amount,
SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;

-- Result example:
sale_date   | amount | running_total
------------|--------|---------------
2025-01-01  | 100    | 100
2025-01-02  | 150    | 250
2025-01-03  | 80     | 330

-- Rank of each person
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

-- Result:
name    | salary | rank | dense_rank
--------|--------|------|------------
King    | 12000  | 1    | 1
Alice   | 9000   | 2    | 2
Bob     | 9000   | 2    | 2          
Charlie | 7000   | 4    | 3

dense_rank doesn't skip

-- Previous / next row value (great for time series)
SELECT sale_date, amount,
LAG(amount) OVER (ORDER BY sale_date)  AS previous_day_amount,
LEAD(amount) OVER (ORDER BY sale_date) AS next_day_amount
FROM sales;

Quick guide to not get confused:

JOIN = glue 2 tables together horizontally
Window = look around your row (up/down/same group) and bring back smart numbers