INNER JOIN & Matching Relationships — SQL Course

What you'll learn

Join related tables with explicit predicates, qualified columns, and cardinality awareness. 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 INNER JOIN to a realistic data question
Apply ON predicates to a realistic data question
Apply Table aliases to a realistic data question
Apply One-to-many joins 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
Join predicate	The condition matching rows between inputs	Join keys explicitly and qualify every ambiguous column
Cardinality	How many rows may match on each side	Predict one-to-one, one-to-many, or many-to-many before running
INNER JOIN	Only matched combinations survive	Use when unmatched rows are outside the requested result

Professional workflow

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

State the matching join 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

List books with their authors

Two explicit joins traverse the many-to-many relationship and preserve one row per book-author pair.

book_authors_report.sql

SELECT
  b.id AS book_id,
  b.title,
  a.id AS author_id,
  a.display_name,
  ba.author_position
FROM publishing.books AS b
JOIN publishing.book_authors AS ba ON ba.book_id = b.id
JOIN publishing.authors AS a ON a.id = ba.author_id
ORDER BY b.id, ba.author_position;

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

Omitting or weakening an ON predicate creates a Cartesian multiplication that may look like legitimate duplicates.

Independent workshop

Build a review-ready matching join lab against the course commerce dataset.

Your finished workshop must include:

INNER JOIN
ON predicates
Table aliases
One-to-many joins
Many-to-many joins
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

Join predicate: Join keys explicitly and qualify every ambiguous column
Cardinality: Predict one-to-one, one-to-many, or many-to-many before running
INNER JOIN: Use when unmatched rows are outside the requested result

Quick check

1. Which rule best applies to Join predicate?

2. Which rule best applies to Cardinality?

3. Which rule best applies to INNER JOIN?

Next: LEFT, RIGHT, FULL, CROSS & SELF JOIN