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.