← Back to Series Overview
SQL for Every Data Platform · Part 3

Joins & Aggregations

Two operations turn related tables into an answer: JOIN combines rows across tables; aggregation collapses many rows into a summary.

In 60 seconds

The join types and aggregate functions that cover almost every query:

  1. INNER JOIN — only rows matching in both tables.
  2. LEFT JOIN — every row from the left table, NULL where there's no match.
  3. SELF JOIN — a table joined to itself, for hierarchies or pairs within one entity.
  4. GROUP BY collapses rows; HAVING filters the resulting groups (after aggregation) — WHERE filters rows before.
  5. COUNT(DISTINCT col) counts unique non-NULL values only.

The Core Aggregate Functions Every Analyst Uses Daily

Before JOINs enter the picture at all, five functions cover most of the exploratory data analysis (EDA) a data analyst does on a single table: COUNT(), SUM(), AVG(), MIN(), and MAX(). COUNT(*) counts rows, including ones with NULLs in every column; COUNT(column) counts only the rows where that specific column is not NULL; COUNT(DISTINCT column) counts unique non-NULL values, so duplicates collapse to one. SUM() adds up a numeric column; AVG() computes its arithmetic mean — note there is no separate MEAN() function in SQL, "mean" and "average" are the same thing and AVG() is how you get it. MIN()/MAX() find the smallest/largest value and work on numbers, dates, and even text (alphabetically) alike. All five ignore NULL values in the column they're aggregating — the one exception is COUNT(*), which counts every row regardless of what's in it. DISTINCT on its own (outside of COUNT) removes duplicate rows from a plain SELECT, the same de-duplication idea applied to the whole result instead of just a count.

SQL · the five core aggregates, no JOIN needed

-- A single-table EDA pass: shape of the products table at a glance
SELECT
    COUNT(*)                   AS total_rows,
    COUNT(DISTINCT category)   AS distinct_categories,
    SUM(stock_quantity)        AS total_units_in_stock,
    AVG(price)                 AS average_price,
    MIN(price)                 AS cheapest_price,
    MAX(price)                 AS most_expensive_price
FROM products;

-- DISTINCT on its own: unique category names, no counting involved
SELECT DISTINCT category FROM products;

-- COUNT(column) vs. COUNT(*): only phone is nullable, so these two differ
SELECT COUNT(*) AS all_customers, COUNT(phone) AS customers_with_phone
FROM customers;

Choosing the Right JOIN

The most common analytics shape is a chain: customers → orders → order_items → products. Be explicit about JOIN type — a silent INNER JOIN where you meant LEFT JOIN quietly drops exactly the rows you were often trying to measure (customers with zero orders, products never sold). An INNER JOIN asks "give me rows that exist on both sides"; a LEFT JOIN asks "give me everything on the left, and whatever matches on the right, or blanks if nothing does." Reaching for the wrong one is the single most common correctness bug in day-to-day analytics SQL — not a syntax error, just a silently wrong number that looks plausible.

SQL · INNER vs. LEFT vs. SELF

-- INNER JOIN: only customers who have placed at least one order
SELECT c.name, o.id AS order_id, o.order_date, o.status
FROM customers c
JOIN orders o ON o.customer_id = c.id;

-- LEFT JOIN: every customer, INCLUDING the ones with zero orders
SELECT c.id, c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;               -- isolates customers with no orders at all

-- SELF JOIN: pair employees in the same department (excluding self-pairs)
SELECT e1.name AS employee_a, e2.name AS employee_b, e1.department
FROM employees e1
JOIN employees e2 ON e1.department = e2.department AND e1.id < e2.id;
Which rows survive each JOIN type INNER JOIN Matching rows only LEFT JOIN All of left; right cols NULL if unmatched RIGHT JOIN All of right; left cols NULL if unmatched FULL JOIN Both sides; NULL on whichever didn't match

A JOIN result row always has columns from both tables — LEFT/RIGHT/FULL don't drop the unmatched side's columns, they fill them with NULL. That's different from a UNION (Part 7), which stacks same-shaped rows instead of combining columns side by side — a FULL JOIN of two tables is not the same thing as a UNION of them, even though this diagram's shading looks similar for both. SQLite also has no native RIGHT/FULL JOIN — swap table order with LEFT JOIN, or use a platform from Part 12 that supports them directly.

customers LEFT JOIN orders — the row still has order columns, just NULL NAME ORDER_ID ORDER_DATE STATUS Alice Martin 1 2023-01-20 delivered matched Walter Bell NULL NULL NULL unmatched, kept anyway Same 4 columns on every row -- if this were "just the left table," order_id/order_date/status wouldn't be columns at all.

Walter Bell has no orders, but the row survives with three NULLs — not because the right table vanished, but because nothing matched it.

WHERE vs. HAVING

WHERE runs before grouping and filters individual rows. HAVING runs after grouping and filters the aggregated groups. Both can appear in the same query: WHERE to exclude rows first, HAVING to filter the resulting summary. The rule of thumb: if the condition touches a raw column (status = 'delivered'), it goes in WHERE; if it touches the result of an aggregate function (SUM(...) > 1000), it has to go in HAVING — WHERE runs too early in the engine's logical order to see a group total that doesn't exist yet. Filtering with WHERE before grouping is also the faster choice whenever it's an option, since fewer rows reach the (usually more expensive) grouping step.

SQL · GROUP BY, HAVING, COUNT(DISTINCT)

-- Revenue by category: JOIN + GROUP BY
SELECT p.category,
       COUNT(DISTINCT o.id)                       AS orders,
       ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM order_items oi
JOIN orders o   ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
GROUP BY p.category
ORDER BY revenue DESC;

-- HAVING filters the GROUPED result -- WHERE can't reference an aggregate
SELECT p.category, ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM order_items oi
JOIN products p ON p.id = oi.product_id
GROUP BY p.category
HAVING revenue > 1000
ORDER BY revenue DESC;

Index What You Join and Filter On

An index is a separate, sorted lookup structure the engine maintains alongside a table, so it can jump straight to the rows matching a value instead of reading every row to check. Without one, every JOIN ... ON condition and every WHERE filter forces a full scan of the table — fine at a few thousand rows, painfully slow at millions. Joining on indexed columns is the single biggest lever for query speed at scale — Part 9 (Performance & Tuning) shows exactly why, with a real before/after query plan.

SQL · index the columns a JOIN or WHERE actually uses

-- Without these, every JOIN below forces a full scan of orders and order_items
CREATE INDEX idx_orders_customer_id   ON orders(customer_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

-- Now this JOIN chain can jump straight to matching rows instead of scanning:
SELECT c.name, SUM(oi.quantity * oi.unit_price) AS spend
FROM customers c
JOIN orders o       ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id   = o.id
GROUP BY c.name;
-- See Part 9 for the real EXPLAIN QUERY PLAN, before and after this index
Join, then collapse: the shape of most analytics queries 👤 Customers dim table 📦 Orders fact rows 🧾 Order Items JOIN chain 🔢 GROUP BY Aggregate

A JOIN chain feeds an aggregation — the two operations that answer almost every question.

The takeaway: Every non-aggregated column in SELECT must appear in GROUP BY — some engines are lenient here (a gap that hides mistakes a stricter warehouse would reject outright).

Runnable code for this part: sql-for-data-platforms on GitHub — Module 3 of 14.

← Part 2 Designing the Schema: Star vs. Snowflake
← Back to Publications