SQL
Intermediate 15 min read SQL

Joining for Analysis

Combining tables without multiplying rows

Joins combine tables for analysis. The join type, keys and cardinalities you choose determine both correctness and row counts — a multiply is a signature of a wrong join.

JOININNERLEFTFULLkeyscardinalitygrain
Prerequisites: Aggregates and Grouping Fact and Dimension Tables
Before we start

What you will learn

  • Choose INNER, LEFT and FULL joins by analysis intent
  • Identify join-key and cardinality problems before they inflate results
  • Use lookups to preserve grain when a join is many-to-many
Introduction

What is this?

Joining is how facts meet dimensions (and occasionally facts meet facts). The join type decides which un-matched rows survive; the keys decide if a row multiplies. In BI the common mistake is a many-to-many join silently inflating a sum — known as the 'fan trap'.

Motivation

Why do you need it?

  • Reports live or die by joins: one bad join flips every sum on the page.
  • LEFT vs INNER systematically changes totals for partial data.
  • Understanding cardinality lets you catch the fan trap before a wrong number ships.
  • Dimensional modeling exists largely to make joins clean one-to-many.
Think about it

Mental model

Imagine

Merging customer lists at a reunion

Think of

SQL joins

INNER shows people on both lists; LEFT keeps everyone from the first list and fills in gaps; the hazards start when name collisions (keys) match wrong people or duplicates match to multiples — the fan trap of a reunion.

Mechanics

How it works

  • INNER JOIN keeps rows where both keys match.
  • LEFT JOIN keeps all left rows, NULLs where the right is missing.
  • RIGHT/FULL JOIN complete the complement (FULL keeps both sides).
  • Keys must be unique on the 'one' side for clean one-to-many joins.
  • Many-to-many joins multiply rows; the correct fix is to deduplicate or summarize to the target grain.

Join outcomes by type.

INNER
LEFT
FULL
Many-to-many
Clean 1:M
Hands-on

Examples

The fan trap and its fix

The fan trap and its fix
-- Wrong: joining orders to order_lines multiplies each order rows
SELECT o.order_id, SUM(l.amount) FROM orders o JOIN order_lines l ON o.order_id = l.order_id GROUP BY o.order_id;
 
-- Right: summarize the many side first, then join
WITH line_totals AS (SELECT order_id, SUM(amount) AS amount FROM order_lines GROUP BY order_id)
SELECT o.order_id, lt.amount FROM orders o LEFT JOIN line_totals lt ON o.order_id = lt.order_id;

Option one sums line amounts but repeats the order once per line; if you then SUM order amounts again, you double-count. Pre-aggregating the lines preserves grain.

Real world

In production

  • A daily revenue query joins fact orders (one row per order) to dim customer/date (one-for-one) — clean 1:1.
  • An attribution report LEFT JOINs campaigns so un-touched orders still appear with NULL campaign.
  • A ledger push FULL JOINs warehouse and finance tables to expose exactly which rows are missing on each side.
Guidelines

Best practices

  • Join facts to dimensions; keep 1:1 or 1:M with the 'one' side unique on its key.
  • Make explicit which side is the 'truth' and which is enrichment (truth is usually the fact).
  • Verify cardinality before trusting a join: count distinct keys on each side.
  • Use LEFT JOIN when the right side is optional and you must keep ALL left rows.
Watch out

Common mistakes

  • Joining facts-to-facts without fixing grain — every sum balloons.
  • Duplicate keys in a dimension silently multiplying fact rows.
  • RIGHT JOIN where a LEFT would be clearer, obscuring intent.
  • Using INNER JOIN when your report must highlight missing data.
Root cause analysis

Common issues

Error
Total revenue is 3x the source
Root cause

Left join to a dimension with duplicated keys

Fix

Dedupe the dimension or use a surrogate key; assert key uniqueness in tests.

Error
Orders disappear from report
Root cause

INNER JOIN drops orders with no matching row

Fix

Use LEFT JOIN from the fact and populate a valid default on the dimension side.

When things break

Debugging tips

  • Row count exploded? Drop the GROUP BY temporarily to see the raw multiplication.
  • Diff LEFT vs INNER totals to quantify how much data is missing on the right.
  • Check duplicate keys: SELECT key, COUNT(*) ... HAVING COUNT(*) > 1.
Interviews

Interview checkpoint

Practice

Hands-on lab

Diagnose and Fix a Fan Trap
35 min · intermediate
In progress
Objectives
  • Reproduce a fan-trap multiplication with a real dataset
  • Fix grain with a pre-aggregated CTE
  • Verify sums reconcile to the source fact totals

Diagnose and Fix a Fan Trap

35 min · intermediate

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

Quick quiz

0/2
1

Your orders-to-region join doubles the order count. Most likely cause?

2

A LEFT JOIN can never increase the number of rows beyond the left table's rows.

Go deeper

Official documentation

PostgreSQL JOIN documentation
docs