Module 03 // Relational Mapping

Joins & Aggregations

> Combining rows across tables — the heart of relational queries. From INNER joins to OUTER variations and grouped aggregates.

Why Joins?

Normalized data lives in multiple tables. JOIN reconnects rows based on a shared key — typically a foreign key referencing another table's primary key.

INNER JOIN

Returns only rows that match in both tables.

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

LEFT JOIN

Returns all rows from the left table, even if there's no match on the right. Unmatched right-side columns are NULL.

-- Find employees, including those without a department
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

-- Find employees with NO department
SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.id IS NULL;

RIGHT & FULL OUTER JOIN

Symmetric counterpart and the union of both.

-- All departments, even empty ones
SELECT d.dept_name, e.name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

-- Everything from both sides
SELECT e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

Joins + Aggregations

The most common analytical pattern: join, group, aggregate.

SELECT d.dept_name,
       COUNT(e.id) AS headcount,
       AVG(e.salary) AS avg_salary
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
GROUP BY d.dept_name
ORDER BY headcount DESC;

Window Functions

Aggregations that don't collapse rows. Use OVER() with PARTITION BY to compute per-group metrics inline.

SELECT name,
       department,
       salary,
       AVG(salary) OVER (PARTITION BY department) AS dept_avg,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;