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. See the dedicated Window Functions module for ranking, frames, LAG/LEAD, running totals, and sessionization patterns.
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;CROSS JOIN — Cartesian Product
Every row from the left paired with every row from the right. Useful for generating grids (e.g. date × product) and combinatorial reports.
-- Every product on every day, even days with no sales
SELECT d.day, p.sku, COALESCE(SUM(s.qty), 0) AS units
FROM generate_series('2025-01-01'::date, '2025-01-31'::date, '1 day') AS d(day)
CROSS JOIN products p
LEFT JOIN sales s ON s.day = d.day AND s.sku = p.sku
GROUP BY d.day, p.sku;Self Join
A table joined to itself. Reach for it when rows reference other rows in the same table — employee/manager, parent/child, before/after.
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;Semi & Anti Joins (EXISTS / NOT EXISTS)
"Does a matching row exist?" without pulling columns from the other side. Faster than a JOIN + DISTINCT and immune to row multiplication.
-- Customers who placed at least one order (semi-join)
SELECT c.*
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
-- Customers who never ordered (anti-join)
SELECT c.*
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);USING and NATURAL JOIN
Shortcuts when the join column has the same name in both tables. USING is explicit and safe; NATURAL JOIN auto-joins on all same-named columns and is usually a footgun.
-- USING collapses the join column into one
SELECT name, dept_name
FROM employees JOIN departments USING (dept_id);Join Performance — What to Watch
- Index the column on the right side of each join condition (foreign keys).
- The planner picks between nested loop (small outer + indexed inner), hash join (large unsorted equi-join), and merge join (both sides already sorted on the key).
- A LEFT JOIN with a
WHERE right.col = ...silently becomes an INNER JOIN. Put right-side filters in theONclause instead. - Read
EXPLAIN ANALYZE— the join type and row estimates tell you what to fix.