SQL for Analytics
Intermediate 14 min read SQL for Analytics

SQL Joins

Combining tables without inventing rows

Joins combine tables on a key: inner keeps matches, left keeps all left rows, and the JOIN type plus key cardinality decide whether rows multiply. Getting joins right prevents the classic 'doubled revenue' bug.

JOINinner joinleft joinkey cardinalityfanoutrow multiplication
Prerequisites: SQL Aggregations
Before we start

What you will learn

  • Choose INNER vs LEFT vs FULL joins
  • Detect and prevent row fan-out (multiplication)
  • Trace joins to grains to explain unexpected row counts
Introduction

What is this?

A JOIN combines rows from two tables where a condition matches. INNER JOIN keeps only matches; LEFT JOIN keeps all left rows plus matches (or NULLs); FULL OUTER JOIN keeps both sides. The subtle failure is cardinality: if the right table has two rows per key, the left row appears twice — a fan-out that silently doubles revenue unless you aggregate to the right grain.

Motivation

Why do you need it?

  • Real analytical questions span facts, dimensions and users — joined tables.
  • A wrong join multiplies rows and produces confident but false numbers.
  • Knowing join semantics lets you explain any unexpected row count.
  • Joins underpin cohort tables, funnel analysis and customer 360 views.
Think about it

Mental model

Imagine

Combining a registry of names with a registry of addresses

Think of

JOINs

Match people (key) to homes. Inner = only people with addresses; left = everyone, blanks where no address; a family of three sharing one address office-copy = the fan-out that multiplies people.

Mechanics

How it works

  • You name the key: ON orders.user_id = users.id, and the type decides match policy.
  • INNER discards unmatched rows from both sides.
  • LEFT keeps all left rows, putting NULLs where no match exists.
  • Fan-out: one left row matched by N right rows yields N output rows.
  • Aggregate after the join (GROUP BY user_id → SUM(amount)) to undo the multiplication, or pre-aggregate the many-side table.

The four join behaviors visualized as sets.

Left table
INNER
LEFT
FULL
Fan-out check
Hands-on

Examples

The classic premature-sum trap

The classic premature-sum trap
-- Fan-out: orders JOIN order_items → each order appears once per item
SELECT o.user_id, SUM(o.total)
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
GROUP BY 1;           -- WRONG: o.total is summed multiple times
 
-- Correct: aggregate items, then join — or pre-aggregate items
SELECT o.user_id, SUM(i.qty * i.price) revenue
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
WHERE i.status != 'cancelled'
GROUP BY 1;

The fix either avoids touching the multiplicate table with your SUM, or ensures every column you sum is at the post-join grain.

Real world

In production

  • Order records join user dimension for cohort membership.
  • Event joins product dimension to bucket activity by category.
  • A LEFT JOIN from users to orders finds zero-purchase users for activation work.
Guidelines

Best practices

  • Always state the grain of each table before joining and re-assert after.
  • Prefer pre-aggregation of the many-side before joining to the one-side.
  • Use LEFT JOIN deliberately; an INNER that drops unmatchable rows can hide data loss.
  • Test small: join two tiny samples by hand before trusting the big query.
Watch out

Common mistakes

  • Summing a column from the one-side across a many-side fan-out.
  • Joining on a non-unique key and creating an accidental cartesian pair.
  • Assuming every row has a match, then an INNER silently shrinks the universe.
  • Joining on text keys with case/whitespace mismatch that drops matches.
When things break

Debugging tips

  • Row count exploded? Check the right table's key cardinality with SELECT key, COUNT(*).
  • Rows missing? Try LEFT JOIN to see which side lacks matches.
  • Dollar sum doubled? One of your join keys is not unique on one side.
  • Join keys with NULL never match — inspect NULL keys explicitly.
Interviews

Interview checkpoint

Practice

Hands-on lab

Diagnose Join Fan-Out With SQLite
30 min · intermediate
In progress
Objectives
  • Join a one-side to a many-side table
  • Reproduce and then fix double counting
  • Use LEFT and INNER joins deliberately

Diagnose Join Fan-Out With SQLite

30 min · intermediate

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

A query JOINs orders (one row per order) to a table with two rows per order. What likely happens to SUM(order_total)?

2

LEFT JOIN always preserves every row from the left table.

Go deeper

Official documentation

PostgreSQL JOIN documentation
docs