Chapter 07 · Joining Related Tables
Join Cardinality, Duplicates, and Debugging Incorrect Results
Unexpected duplicates are usually evidence about relationship cardinality or predicate quality—not a reason to add DISTINCT blindly.
Learning outcomes
A join result can be syntactically valid and still be logically wrong. Reliable debugging starts by stating expected cardinality, proving uniqueness, and measuring row counts before adding more tables.
Predict one-to-one, one-to-many, and many-to-many result shapes.
Diagnose incomplete predicates and fan-out multiplication.
Distinguish legitimate repeated values from accidental duplicate row pairs.
Apply a repeatable join-debugging workflow before using DISTINCT.
Cardinality is the contract
| Relationship | Expected matches per left row | Typical example |
|---|---|---|
| Many-to-one | Zero or one parent | sale → customer |
| One-to-many | Zero to many children | customer → sale |
| One-to-one | Zero or one counterpart on both sides | user → profile with unique foreign key |
| Many-to-many | Many on both sides through a bridge | student ↔ course through enrollment |
| Range match | Usually exactly one governed interval | sale amount → price band |
Before executing a join, write the expected relationship in words. Unexpected row counts then become a test failure rather than a surprise.
Measure after every join
SELECT COUNT(*) AS sale_rowsFROM sale;SELECT COUNT(*) AS after_customer_joinFROM sale AS sJOIN customer AS c ON c.customer_id = s.customer_id;SELECT COUNT(*) AS after_product_joinFROM sale AS sJOIN customer AS c ON c.customer_id = s.customer_idJOIN product AS p ON p.product_id = s.product_id;Both joins are many-to-one from sale, so six input sale rows should remain six. A larger count means a supposed parent key is not unique or the predicate is incomplete.
Prove uniqueness on join keys
SELECT customer_id, COUNT(*) AS row_countFROM customerGROUP BY customer_idHAVING COUNT(*) > 1;SELECT warehouse_code, product_id, COUNT(*) AS row_countFROM warehouse_stockGROUP BY warehouse_code, product_idHAVING COUNT(*) > 1;Primary and unique constraints should make these queries return no rows. For external or staging data, run the checks explicitly before joining.
Incomplete composite predicates multiply rows
SELECT ws.warehouse_code AS stock_warehouse, rt.warehouse_code AS target_warehouse, ws.product_id, ws.stock_qty, rt.target_qtyFROM warehouse_stock AS wsJOIN reorder_target AS rt ON rt.product_id = ws.product_idORDER BY ws.product_id, stock_warehouse, target_warehouse;SELECT ws.warehouse_code, ws.product_id, ws.stock_qty, rt.target_qtyFROM warehouse_stock AS wsJOIN reorder_target AS rt ON rt.warehouse_code = ws.warehouse_code AND rt.product_id = ws.product_idORDER BY ws.warehouse_code, ws.product_id;The wrong query combines W1 stock with W2 targets whenever product IDs match. The extra rows are logical consequences of the incomplete predicate.
Fan-out from two child collections
Joining two independent one-to-many relationships through the same parent creates the product of child counts for each parent.
SELECT c.customer_id, c.full_name, cc.contact_id, s.sale_idFROM customer AS cLEFT JOIN customer_contact AS cc ON cc.customer_id = c.customer_idLEFT JOIN sale AS s ON s.customer_id = c.customer_idORDER BY c.customer_id, cc.contact_id, s.sale_id;Nadia has two contacts and two sales, producing four joined rows. No row is a byte-for-byte duplicate; each contact-sale pair is distinct. DISTINCT cannot repair the underlying grain mismatch.
Aggregate each child to the desired grain first
WITH contact_summary AS ( SELECT customer_id, COUNT(*) AS contact_count FROM customer_contact GROUP BY customer_id),sale_summary AS ( SELECT customer_id, COUNT(*) AS sale_count, SUM(quantity) AS units_bought FROM sale GROUP BY customer_id)SELECT c.customer_id, c.full_name, COALESCE(cs.contact_count, 0) AS contact_count, COALESCE(ss.sale_count, 0) AS sale_count, COALESCE(ss.units_bought, 0) AS units_boughtFROM customer AS cLEFT JOIN contact_summary AS cs ON cs.customer_id = c.customer_idLEFT JOIN sale_summary AS ss ON ss.customer_id = c.customer_idORDER BY c.customer_id;Each CTE declares one row per customer, so the final joins remain one-to-one at the report grain.
Payments demonstrate another fan-out risk
SELECT c.customer_id, SUM(p.unit_price * s.quantity) AS repeated_sales_value, SUM(pay.amount) AS payment_valueFROM customer AS cJOIN sale AS s ON s.customer_id = c.customer_idJOIN product AS p ON p.product_id = s.product_idLEFT JOIN payment AS pay ON pay.sale_id = s.sale_idGROUP BY c.customer_idORDER BY c.customer_id;A sale with two payments repeats the sale amount twice. Aggregate payments per sale first, or calculate the sale-side measure in a separate summary.
WITH sale_value AS ( SELECT s.sale_id, s.customer_id, p.unit_price * s.quantity AS gross_amount FROM sale AS s JOIN product AS p ON p.product_id = s.product_id),payment_summary AS ( SELECT sale_id, SUM(amount) AS paid_amount FROM payment GROUP BY sale_id)SELECT sv.customer_id, ROUND(SUM(sv.gross_amount), 2) AS gross_sales, ROUND(SUM(COALESCE(ps.paid_amount, 0)), 2) AS payments_receivedFROM sale_value AS svLEFT JOIN payment_summary AS ps ON ps.sale_id = sv.sale_idGROUP BY sv.customer_idORDER BY sv.customer_id;A disciplined debugging workflow
State the grain
Write “one row per …” for every input and the required output.
Check uniqueness
Group by each proposed parent or composite key and search for counts above one.
Join incrementally
Add one relationship at a time and compare row counts and distinct keys.
Inspect a small key
Filter to one entity whose child counts are known and enumerate the pairs.
Align grains
Aggregate or deduplicate intentionally before combining independent child collections.
Use DISTINCT last
Only remove duplicates when duplicate rows are truly semantically interchangeable.
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.
Chapter capstone
Produce one row per customer with contact count, sale count, gross sales, payments received, and outstanding amount. Customers without activity must remain visible.
WITH contact_summary AS ( SELECT customer_id, COUNT(*) AS contact_count FROM customer_contact GROUP BY customer_id),sale_summary AS ( SELECT s.customer_id, COUNT(*) AS sale_count, SUM(p.unit_price * s.quantity) AS gross_sales FROM sale AS s JOIN product AS p ON p.product_id = s.product_id GROUP BY s.customer_id),payment_summary AS ( SELECT s.customer_id, SUM(pay.amount) AS payments_received FROM sale AS s JOIN payment AS pay ON pay.sale_id = s.sale_id GROUP BY s.customer_id)SELECT c.customer_id, c.full_name, COALESCE(cs.contact_count, 0) AS contact_count, COALESCE(ss.sale_count, 0) AS sale_count, ROUND(COALESCE(ss.gross_sales, 0), 2) AS gross_sales, ROUND(COALESCE(ps.payments_received, 0), 2) AS payments_received, ROUND( COALESCE(ss.gross_sales, 0) - COALESCE(ps.payments_received, 0), 2 ) AS outstanding_amountFROM customer AS cLEFT JOIN contact_summary AS cs ON cs.customer_id = c.customer_idLEFT JOIN sale_summary AS ss ON ss.customer_id = c.customer_idLEFT JOIN payment_summary AS ps ON ps.customer_id = c.customer_idORDER BY c.customer_id;Checkpoint
Debug before deduplicating
- Why can two independent child joins create a Cartesian effect within each parent?
- What does “grain” mean in a query result?
- How do you prove a proposed parent key is unique?
- Why is DISTINCT usually the wrong first response to unexpected rows?
- How does pre-aggregation prevent fan-out?
Review the answers
Each parent receives every combination of its child rows. Grain is what one output row represents. Group by the key and search for counts above one. DISTINCT hides symptoms and may remove legitimate rows. Pre-aggregation reduces each child source to one row at the required join grain.
Chapter 7 summary
- Joins create matched row pairs according to explicit predicates.
- Inner joins retain matches; outer joins preserve selected unmatched rows.
- CROSS, self, and non-equi joins model combinations, hierarchies, and ranges.
- Result multiplicity follows relationship cardinality.
- Incomplete predicates and independent child collections are common causes of unexpected row multiplication.
- Reliable debugging starts with grain, uniqueness, incremental counts, and pre-aggregation.
Chapter 8 introduces aggregate functions and grouping semantics, where row grain changes from detail records to groups.