Chapter 07 · Joining Related Tables
Why Joins Exist and How Matching Works
Treat a join as a controlled row-matching operation whose correctness depends on keys, predicates, and relationship cardinality.
Learning outcomes
Normalization stores each fact once, but useful questions often need facts from several tables. A join reconstructs a wider result by pairing rows that satisfy a relationship predicate.
Explain why related facts are separated and later recombined.
Describe a join as candidate row pairs followed by predicate evaluation.
Identify the preserved and discarded rows in an inner join.
Predict how one-to-many relationships affect result row counts.
Why joins exist
One fact, one home
Customer names belong in customer; sale events belong in sale. Repeating customer details in every sale creates update anomalies.
Relationship through keys
The child foreign key identifies which parent row supplies the related facts.
Question-specific shape
A query can combine only the columns required for one report without changing storage design.
Conceptually, SQL considers row pairs and keeps the pairs whose join condition is TRUE. Optimizers use indexes and algorithms rather than literally constructing every pair, but the logical result is the same.
The minimum useful join
SELECT s.sale_id, s.sold_at, c.customer_id, c.full_nameFROM sale AS sJOIN customer AS c ON c.customer_id = s.customer_idORDER BY s.sale_id;The foreign key value in sale.customer_id is compared with the candidate parent key in customer.customer_id. Only matching row pairs survive this inner join.
Matching is not column copying
| Term | Meaning | Failure mode |
|---|---|---|
| Join input | A table, view, subquery, CTE, or previous join result | Assuming a join can only involve base tables. |
| Join predicate | A boolean expression in ON or USING | Omitting part of the relationship or comparing unrelated columns. |
| Matched pair | A left row and right row for which the predicate is TRUE | Expecting SQL to infer semantic relationships from similar names. |
| Projection | The columns selected from the matched pair | Selecting ambiguous unqualified names or duplicate columns. |
| Cardinality | How many matching rows exist on each side | Assuming every relationship is one-to-one. |
One-to-many expansion
SELECT c.customer_id, c.full_name, s.sale_id, s.sold_atFROM customer AS cJOIN sale AS s ON s.customer_id = c.customer_idORDER BY c.customer_id, s.sale_id;Nadia has two sales, so her customer row contributes to two output rows. The join does not duplicate the stored customer row; the result contains two distinct matched pairs.
SELECT c.customer_id, c.full_name, COUNT(*) AS matched_sale_rowsFROM customer AS cJOIN sale AS s ON s.customer_id = c.customer_idGROUP BY c.customer_id, c.full_nameORDER BY c.customer_id;Old comma syntax versus explicit JOIN
SELECT s.sale_id, c.full_nameFROM sale AS s, customer AS cWHERE c.customer_id = s.customer_id;The comma form places relationship logic in WHERE beside ordinary filters. Explicit JOIN ... ON makes the relationship visible and is safer when queries grow.
Six sales multiplied by five customers creates 30 row pairs. SQL cannot know that this was accidental.
SELECT COUNT(*) AS pair_countFROM sale AS sCROSS JOIN customer AS c;NULL and three-valued join logic
A join predicate keeps a pair only when it evaluates to TRUE. FALSE and UNKNOWN are both rejected by an inner join. Equality with NULL is UNKNOWN, so nullable relationship values require deliberate handling.
SELECT e.employee_name, m.employee_name AS manager_nameFROM employee AS eJOIN employee AS m ON m.employee_id = e.manager_idORDER BY e.employee_id;The director has manager_id = NULL, so no row pair satisfies the equality and the director is absent. Lesson 3 uses an outer join to preserve that row.
Practice database
Run this setup once in a disposable SQLite database. Every example in Chapter 7 uses these tables, keys, and deliberately varied relationship shapes.
PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS payment;DROP TABLE IF EXISTS customer_contact;DROP TABLE IF EXISTS reorder_target;DROP TABLE IF EXISTS warehouse_stock;DROP TABLE IF EXISTS price_band;DROP TABLE IF EXISTS employee;DROP TABLE IF EXISTS sale;DROP TABLE IF EXISTS product;DROP TABLE IF EXISTS customer;CREATE TABLE customer ( customer_id INTEGER PRIMARY KEY, full_name TEXT NOT NULL, city TEXT, segment TEXT NOT NULL CHECK (segment IN ('consumer', 'business'))) STRICT;CREATE TABLE product ( product_id INTEGER PRIMARY KEY, sku TEXT NOT NULL UNIQUE, product_name TEXT NOT NULL, category TEXT NOT NULL, unit_price REAL NOT NULL CHECK (unit_price >= 0)) STRICT;CREATE TABLE sale ( sale_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customer(customer_id), product_id INTEGER NOT NULL REFERENCES product(product_id), quantity INTEGER NOT NULL CHECK (quantity > 0), sold_at TEXT NOT NULL, channel TEXT NOT NULL CHECK (channel IN ('web', 'partner', 'direct'))) STRICT;CREATE TABLE employee ( employee_id INTEGER PRIMARY KEY, employee_name TEXT NOT NULL, manager_id INTEGER REFERENCES employee(employee_id)) STRICT;CREATE TABLE price_band ( band_name TEXT PRIMARY KEY, minimum_amount REAL NOT NULL, maximum_amount REAL, CHECK (maximum_amount IS NULL OR maximum_amount > minimum_amount)) STRICT;CREATE TABLE warehouse_stock ( warehouse_code TEXT NOT NULL, product_id INTEGER NOT NULL REFERENCES product(product_id), stock_qty INTEGER NOT NULL CHECK (stock_qty >= 0), PRIMARY KEY (warehouse_code, product_id)) STRICT;CREATE TABLE reorder_target ( warehouse_code TEXT NOT NULL, product_id INTEGER NOT NULL REFERENCES product(product_id), target_qty INTEGER NOT NULL CHECK (target_qty >= 0), PRIMARY KEY (warehouse_code, product_id)) STRICT;CREATE TABLE customer_contact ( contact_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customer(customer_id), contact_type TEXT NOT NULL, contact_value TEXT NOT NULL) STRICT;CREATE TABLE payment ( payment_id INTEGER PRIMARY KEY, sale_id INTEGER NOT NULL REFERENCES sale(sale_id), amount REAL NOT NULL CHECK (amount > 0), paid_at TEXT NOT NULL) STRICT;INSERT INTO customer VALUES (1, 'Nadia Rahimi', 'Tehran', 'consumer'), (2, 'Omar Haddad', 'Berlin', 'business'), (3, 'Lina Chen', NULL, 'consumer'), (4, 'Ava Morgan', 'Berlin', 'consumer'), (5, 'Noah Silva', 'Lisbon', 'business');INSERT INTO product VALUES (10, 'DB-101', 'Database Foundations', 'course', 49.00), (11, 'SQL-201', 'SQL Query Practice', 'course', 69.00), (12, 'REF-001', 'SQL Reference Card', 'book', 15.00), (13, 'LAB-001', 'SQLite Lab Bundle', 'lab', 29.00), (14, 'DATA-010', 'Data Quality Workbook', 'book', 24.50), (15, 'OPS-301', 'Database Operations', 'course', 89.00);INSERT INTO sale VALUES (100, 1, 10, 1, '2026-08-01 09:15:00', 'web'), (101, 2, 11, 3, '2026-08-01 10:45:00', 'direct'), (102, 1, 12, 2, '2026-08-02 11:30:00', 'web'), (103, 4, 10, 1, '2026-08-03 13:05:00', 'partner'), (104, 2, 13, 2, '2026-08-03 15:20:00', 'direct'), (105, 5, 15, 1, '2026-08-04 16:40:00', 'partner');INSERT INTO employee VALUES (1, 'Maya Director', NULL), (2, 'Reza Engineering', 1), (3, 'Sara Data', 1), (4, 'Jon Analyst', 3), (5, 'Liu Engineer', 2), (6, 'Amir Engineer', 2), (7, 'Eva Intern', 4);INSERT INTO price_band VALUES ('starter', 0, 50), ('standard', 50, 150), ('premium', 150, 500), ('enterprise', 500, NULL);INSERT INTO warehouse_stock VALUES ('W1', 10, 30), ('W1', 11, 18), ('W2', 10, 12), ('W2', 12, 55);INSERT INTO reorder_target VALUES ('W1', 10, 25), ('W1', 12, 40), ('W2', 10, 20), ('W3', 13, 10);INSERT INTO customer_contact VALUES (1, 1, 'email', 'nadia@example.com'), (2, 1, 'phone', '+98-1000'), (3, 2, 'email', 'omar@example.com'), (4, 2, 'phone', '+49-2000'), (5, 3, 'email', 'lina@example.com'), (6, 4, 'email', 'ava@example.com');INSERT INTO payment VALUES (1, 100, 25.00, '2026-08-01 09:20:00'), (2, 100, 24.00, '2026-08-02 09:20:00'), (3, 101, 207.00, '2026-08-01 11:00:00'), (4, 103, 49.00, '2026-08-03 13:20:00'), (5, 105, 50.00, '2026-08-04 17:00:00'), (6, 105, 39.00, '2026-08-05 09:00:00');customer and product are parents of sale. Employees reference their managers in the same table. Warehouse stock and reorder targets use composite keys. Contacts and payments provide one-to-many data for cardinality debugging.
Practice lab
- Join every sale to its customer and product.
- Return only IDs and human-readable names required by the report.
- Order the result so repeated parent rows are easy to inspect.
- Count how many matched sales each participating product has.
- Temporarily remove one join predicate and predict the row count before executing.
SELECT s.sale_id, c.full_name AS customer_name, p.product_name, s.quantity, ROUND(p.unit_price * s.quantity, 2) AS gross_amountFROM sale AS sJOIN customer AS c ON c.customer_id = s.customer_idJOIN product AS p ON p.product_id = s.product_idORDER BY s.sale_id;Checkpoint
Reason about the row pairs
- Why can one customer appear in several joined rows without violating the customer primary key?
- What happens when an inner-join predicate is UNKNOWN?
- Why is an explicit JOIN preferable to comma syntax?
- What row count do six sales and five customers produce without a matching predicate?
Review the answers
A primary key constrains stored customer rows, not occurrences in a query result. Inner joins retain only TRUE matches. Explicit JOIN separates relationships from filters. Six by five produces 30 candidate pairs.
Summary and references
- Joins reconstruct question-specific results from normalized facts.
- A join predicate defines which row pairs represent a relationship.
- Inner joins discard left and right rows that have no TRUE match.
- One-to-many relationships naturally repeat parent values in the output.
- A missing predicate creates a Cartesian product.