Multi-Table Joins & Duplicate Control — SQL Course

What you'll learn

Compose larger join graphs, predict result grain, diagnose accidental multiplication, and avoid DISTINCT as a blind repair. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

By the end of this lesson, you'll be able to:

Apply Query grain to a realistic data question
Apply Join paths to a realistic data question
Apply Fan-out to a realistic data question
Apply Bridge tables to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
Result grain	The entity or event represented by one output row	State it before adding joins
Fan-out	One input row matches multiple rows along more than one path	Aggregate each many-side to the required grain before combining
Semijoin	Test for existence without returning matched rows	Use EXISTS when only presence matters

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the multi-table join graph question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Prevent double-counted order totals

Items and payments are aggregated independently to one row per order before the final join.

order_financials.sql

WITH item_totals AS (
  SELECT order_id, sum(quantity * unit_price) AS ordered
  FROM sales.order_items GROUP BY order_id
), payment_totals AS (
  SELECT order_id, sum(amount) AS paid
  FROM sales.payments GROUP BY order_id
)
SELECT o.id, i.ordered, COALESCE(p.paid, 0) AS paid
FROM sales.orders AS o
JOIN item_totals AS i ON i.order_id = o.id
LEFT JOIN payment_totals AS p ON p.order_id = o.id;

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

Joining two one-to-many tables and aggregating afterward multiplies measures. DISTINCT may hide rows but does not repair totals.

Independent workshop

Build a review-ready multi-table join graph lab against the course commerce dataset.

Your finished workshop must include:

Query grain
Join paths
Fan-out
Bridge tables
Duplicate diagnosis
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

Result grain: State it before adding joins
Fan-out: Aggregate each many-side to the required grain before combining
Semijoin: Use EXISTS when only presence matters

Quick check

1. Which rule best applies to Result grain?

2. Which rule best applies to Fan-out?

3. Which rule best applies to Semijoin?

Next: Aggregate Functions, GROUP BY & HAVING