SQL: Joins & Multi-Table Questions

Chapter roadmap

Connect related tables with reliable join conditions, preserve unmatched rows when needed, model many-to-many relationships, and use subqueries deliberately. Work through the concepts in order, type every example yourself, and keep a short debugging log that records predictions, errors, fixes, and what each fix taught you.

Learning objectives

Write correct inner and outer joins
Prevent accidental row multiplication
Query bridge tables
Use self joins and subqueries

04.1

INNER JOIN

An inner join returns matching row combinations from both tables. The ON clause describes the relationship, normally primary key to foreign key. Qualify columns with table aliases, especially identifiers such as name or id that appear in multiple tables.

Before running the example, trace each expression and write down the expected state or result. Then run it, compare the actual result, and change one input or rule to confirm that you understand which part controls the behavior.

Example

SELECT o.order_id, c.full_name, o.total
FROM orders AS o
JOIN customers AS c
  ON c.customer_id = o.customer_id;

Result

order_id | full_name  | total
4501     | Ana Ruiz   | 72.40
4502     | Jon Bell   | 38.00

Check your understanding

Explain the example in plain language, identify one boundary or failure case, and revise it so the output changes in a predictable way.

04.2

LEFT JOIN and unmatched rows

A left join keeps every row from the left table and fills right-side columns with NULL when no match exists. It answers questions such as customers with no orders. A right-table filter placed in WHERE can accidentally turn a left join into an inner join, so filter placement matters.

Example

SELECT c.customer_id, c.full_name
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

Result

customer_id | full_name
77          | Leila Ortiz

Check your understanding

Explain the example in plain language, identify one boundary or failure case, and revise it so the output changes in a predictable way.

04.3

Many-to-many joins and duplication

Many-to-many relationships use a bridge table containing foreign keys to both related tables. Joining across the bridge creates one row per relationship. Aggregating after such joins requires careful attention to grain so repeated parent values are not counted multiple times.

Example

SELECT s.full_name, co.course_name
FROM students AS s
JOIN enrollments AS e ON e.student_id = s.student_id
JOIN courses AS co ON co.course_id = e.course_id
ORDER BY s.full_name, co.course_name;

Result

full_name | course_name
Ana Ruiz  | Biology
Ana Ruiz  | Computer Science

Check your understanding

Explain the example in plain language, identify one boundary or failure case, and revise it so the output changes in a predictable way.

04.4

Self joins and subqueries

A self join relates rows within one table, such as employees and managers. A subquery supplies a value or set to an outer query. Correlated subqueries run in relation to each outer row and can be expressive, but a join or window function may be clearer and faster.

Example

SELECT employee.full_name, manager.full_name AS manager_name
FROM employees AS employee
LEFT JOIN employees AS manager
  ON manager.employee_id = employee.manager_id;

Result

full_name   | manager_name
Mara Chen   | Noah Williams
Noah Williams | NULL

Check your understanding

Explain the example in plain language, identify one boundary or failure case, and revise it so the output changes in a predictable way.

Chapter assignments

Complete these in order. Later assignments assume that earlier skills are working. Do not only test the sample values; design tests that challenge boundaries, missing values, incorrect types, empty collections, and other likely failures.

A4.1
Join orders, customers, and order items to produce a readable invoice-detail dataset.
Submit readable source code, a brief design note, and evidence from at least three tests including one boundary or invalid case.
A4.2
Find every parent row with no child records using a left join and NULL test.
Submit readable source code, a brief design note, and evidence from at least three tests including one boundary or invalid case.
A4.3
Query a students-courses bridge table and produce both detailed enrollment rows and counts by course.
Submit readable source code, a brief design note, and evidence from at least three tests including one boundary or invalid case.
A4.4
Create a self-join organizational chart showing employee, manager, and manager department.
Submit readable source code, a brief design note, and evidence from at least three tests including one boundary or invalid case.

Chapter project

Streaming Catalog Analysis

Streaming Catalog Analysis — Design or use tables for titles, genres, creators, subscribers, and watch events. Write multi-table reports for catalog coverage, unwatched titles, subscriber behavior, creator reach, and genre popularity without double counting.

Required process

Write a short specification listing inputs, outputs, rules, and failure cases.
Break the work into small functions, queries, modules, or classes appropriate to the language.
Build the smallest working version before adding optional features.
Test normal, boundary, empty, and invalid cases and record the results.
Refactor names and duplication, then write a concise user guide.

Key terms

join
join condition
inner join
outer join
bridge table
many-to-many
alias
self join
subquery