Python Central HubSign in

Data Analytics with Python / Phase 9: SQL for Data Analytics

Joins (INNER, LEFT) for Analytics

Combine tables (users, orders, events) using joins without losing or duplicating rows.

1 min readupdated 6 Oct 2026

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.

SQL
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.

SQL
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
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.

Check your understanding

quizCheck yourself

  1. Which join returns all users, including those with no orders?

    Show answer

    B — users LEFT JOIN orders — LEFT JOIN keeps every row from the left table (users) and fills order columns with NULL when there is no match.

  2. Joining users to events and then summing a user-level amount inflates the metric. What is this problem and fix?

    Show answer

    A — A fan-out duplicating rows, fixed by aggregating first and then joining — Each user has many events, so user rows are duplicated per event. Aggregate first, then join.

  3. Why can a WHERE filter on the right table break a LEFT JOIN?

    Show answer

    A — It removes the NULL rows, turning it into an inner join — Rows with no match have NULLs on the right, and a WHERE condition on them filters those rows out. Put the condition in ON to preserve rows.

  4. What does INNER JOIN keep?

    Show answer

    B — Only rows that match on both sides — INNER JOIN keeps only matching rows.

🧪 Try It Yourself

Exercise 1 – INNER JOIN two tables

exerciseYour turn

Tab indents. Press Escape, then Tab, to leave the editor. Control plus Enter runs the code; Control plus Shift plus Enter submits it.

Output
Press Run to see what your code prints.

Exercise 2 – LEFT JOIN keeps unmatched rows

exerciseYour turn

Tab indents. Press Escape, then Tab, to leave the editor. Control plus Enter runs the code; Control plus Shift plus Enter submits it.

Output
Press Run to see what your code prints.

Exercise 3 – Aggregate before joining to avoid fan-out

exerciseYour turn

Tab indents. Press Escape, then Tab, to leave the editor. Control plus Enter runs the code; Control plus Shift plus Enter submits it.

Output
Press Run to see what your code prints.

Next

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.

Was this page helpful?

Let us know how we did