SQL Mastery
Beginner 15 min read SQL Mastery

Joins In Depth

Which rows survive, and how many times?

INNER, LEFT, RIGHT, FULL and CROSS joins differ only in which unmatched rows survive. Join fan-out — not bugs — is the top cause of doubled metrics.

JOININNERLEFT OUTERfan-outreferential integrityN+1
Prerequisites: SQL Query Fundamentals
Before we start

What you will learn

  • Predict join output sizes for all join types
  • Detect fan-out (row multiplication) and fix it with grain discipline
  • Choose join types from the required row set
Introduction

What is this?

A join combines rows from two tables on a condition. INNER keeps only matches; LEFT OUTER keeps all left rows plus matching right rows (NULLs where absent); RIGHT and FULL mirror that. Every join multiplies rows by matching cardinality — one order matching two line items produces two output rows — and that fan-out is the classic silent count inflation.

Motivation

Why do you need it?

  • Most 'why is the number doubled' postmortems end at a fan-out join.
  • Joins are the bread-and-butter of modeling facts to dimensions.
  • Knowing fan-out lets you reason about output row counts before you run the query.
Think about it

Mental model

Imagine

School attendance registers matched to report cards

Think of

JOIN semantics

INNER pairs only students in both registers; LEFT adds students missing a card (with a blank card); FULL adds cards with no student. And if one student has two cards, you get two rows for them — fan-out.

Mechanics

How it works

  • INNER JOIN: emit row only when the ON condition matches on both sides.
  • LEFT [OUTER] JOIN: keep every left row; unmatched right side becomes NULL columns.
  • RIGHT JOIN is the mirror; FULL JOIN keeps both sides (less common, expensive).
  • CROSS JOIN: every row of A with every row of B — usually a modeling mistake unless intentional.
  • A 1:N relationship (one order, N items) yields N output rows per order; that's expected fan-out.

Join types and their surviving row sets.

INNER
LEFT
FULL
CROSS
Watch fan-out
Hands-on

Examples

Fan-out in action

Fan-out in action
-- one order with two items
SELECT o.id, oi.amount
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.id = 42;
-- returns TWO rows for one order

The join is correct; the output naturally has one row per item. Count-by-order queries must DISTINCT or aggregate at the item level.

Real world

In production

  • A star-schema fact joins many dimensions with surrogate keys — keys are unique so there is no fan-out.
  • A CRM extract LEFT JOINs to keep customers with zero orders visible in the report.
  • A reconciliation LEFT JOINs source to target and flags rows missing on either side as drift.
Guidelines

Best practices

  • Join on keys with known cardinality; document the expected fan-out per join.
  • Count rows before and after a join to catch unintended multiplication.
  • Prefer INNER for facts-dimensions when orphan rows should fail loudly.
  • Add a uniqueness test at the fact grain to prove joins aren't fanning out.
Watch out

Common mistakes

  • Joining a many-to-many relationship without a bridge table, doubling aggregates.
  • Using LEFT JOIN 'to be safe' and silently padding every fact with NULL dims.
  • Joining on non-unique keys by accident (catalog duplicates make one order become N).
Root cause analysis

Common issues

Error
Revenue doubled after adding a join
Root cause

Fan-out: the joined right side had multiple rows per key

Fix

Aggregate the right side to one row per key first, or dedupe the source of the extra rows.

Error
LEFT JOIN produces more left rows than expected
Root cause

Duplicate keys on the left side or multiple matching right rows

Fix

Uniqueness-test the left grain and check right-side cardinality with SELECT key, COUNT(*).

When things break

Debugging tips

  • Doubled number after a join? Count DISTINCT keys on both sides first.
  • When joins slow, check if both keys are indexed and match types (int vs string).
  • NULL padding appearing in a metric = LEFT JOIN where INNER belonged.
Interviews

Interview checkpoint

Practice

Hands-on lab

Predict and Prove Join Fan-Out
25 min · beginner
In progress
Objectives
  • Build order and item tables
  • Run INNER, LEFT and pre-aggregated joins
  • Diagnose and remove fan-out in a revenue query

Predict and Prove Join Fan-Out

25 min · beginner

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

Quick quiz

0/2
1

Orders join to order_items 1:N. How many rows does a query over 1,000 orders produce?

2

A full outer join drops rows that match on both sides.

Go deeper

Official documentation

PostgreSQL joins chapter
docs
SQLite join syntax
docs