Joins (INNER, LEFT) for Analytics

Why joins matter

Analytics questions often require multiple tables.

Example:

Orders are in orders
User attributes are in users

So we join.

INNER JOIN

Keeps only matching rows.

Orders with user country

SELECT
  o.order_id,
  o.amount,
  u.country
FROM orders o
INNER JOIN users u
  ON u.user_id = o.user_id;

LEFT JOIN

Keeps all rows from the left table (even if no match on the right).

Use case: users with or without orders.

Users and their orders (if any)

SELECT
  u.user_id,
  o.order_id,
  o.amount
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.user_id;

Common join pitfalls

1) Duplicated rows

If you join users to events, each user might have many events.

Metrics can explode if you sum after the join.

Fix:

Aggregate first, then join.

2) Filters in WHERE vs ON

With LEFT JOIN, putting conditions on the right table in WHERE can turn it into an inner join.

Prefer:

conditions in the ON clause when you want to preserve rows.

Visualize it

An INNER JOIN keeps only matching rows, while a LEFT JOIN keeps every row from the left table and fills in NULLs when there’s no match.

diagram INNER JOIN vs LEFT JOIN mermaid

  flowchart LR
  A["Table A"] --> C["Table A + Table B"]
  B["Table B"] --> C
  C --> D["INNER JOIN: only rows where key matches in BOTH"]
  C --> E["LEFT JOIN: ALL rows from A, NULLs where B has no match"]

How matching keys affect which rows survive the join

sketch Row-by-row key matching p5.js

Each row on the left looks for a matching user_id on the right; INNER JOIN drops the row that finds no partner.

🧪 Try It Yourself

Exercise 1 – INNER JOIN two tables

Exercise 2 – LEFT JOIN keeps unmatched rows

Exercise 3 – Aggregate before joining to avoid fan-out

Once joins feel natural, Window Functions (OVER, PARTITION BY) let you rank and total rows within groups without collapsing them the way GROUP BY does.

pch.coffeeTagline

pch.coffeeCta

pch.feedbackHeading

pch.feedbackSubheading