Why joins matter

Analytics questions often require multiple tables.

Example:

• Orders are in ordersorders
• User attributes are in usersusers

So we join.

INNER JOIN

Keeps only matching rows.

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

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.

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

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 usersusers to eventsevents, 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 JOINLEFT JOIN, putting conditions on the right table in WHEREWHERE can turn it into an inner join.

Prefer:

• conditions in the ONON 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.

  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"]

🧪 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

Next

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