Chapter 07 · Joining Related Tables
LEFT, RIGHT, and FULL OUTER JOIN
Outer joins answer absence questions, but a misplaced filter can silently turn them back into inner joins.
Learning outcomes
Outer joins preserve unmatched rows from one or both inputs. They are essential for completeness reports, missing-child detection, reconciliation, and optional relationships.
Distinguish preserved and null-supplying sides of LEFT, RIGHT, and FULL joins.
Place filters so optional rows remain visible.
Count matched child rows without counting the synthetic NULL row.
Use FULL OUTER JOIN to reconcile composite-key data sets.
Preservation rules
| Join | Rows always preserved | Unmatched columns supplied as NULL |
|---|---|---|
| LEFT OUTER JOIN | Every row from the left input | Columns from the right input |
| RIGHT OUTER JOIN | Every row from the right input | Columns from the left input |
| FULL OUTER JOIN | Every row from both inputs | Columns from whichever side is missing |
OUTER is optional syntax. LEFT JOIN means LEFT OUTER JOIN.
An unmatched preserved row still contributes exactly one output row. The missing side is represented with NULL values.
LEFT JOIN answers “including none”
SELECT c.customer_id, c.full_name, s.sale_id, s.sold_atFROM customer AS cLEFT JOIN sale AS s ON s.customer_id = c.customer_idORDER BY c.customer_id, s.sale_id;Lina has no sale, but the preserved customer row remains. Every selected sale column is NULL on that row.
SELECT c.customer_id, c.full_nameFROM customer AS cLEFT JOIN sale AS s ON s.customer_id = c.customer_idWHERE s.sale_id IS NULLORDER BY c.customer_id;Test a non-nullable child key such as s.sale_id. Testing a nullable child attribute can misclassify matched rows.
Filter placement changes meaning
SELECT c.full_name, s.sale_id, s.channelFROM customer AS cLEFT JOIN sale AS s ON s.customer_id = c.customer_id AND s.channel = 'web'ORDER BY c.customer_id, s.sale_id;SELECT c.full_name, s.sale_id, s.channelFROM customer AS cLEFT JOIN sale AS s ON s.customer_id = c.customer_idWHERE s.channel = 'web'ORDER BY c.customer_id, s.sale_id;The first query asks, “show every customer and any web sales.” The second asks, “show joined rows whose channel is web,” which excludes customers without a matching web sale.
Count the child key, not the synthetic row
SELECT c.customer_id, c.full_name, COUNT(s.sale_id) AS sale_countFROM customer AS cLEFT JOIN sale AS s ON s.customer_id = c.customer_idGROUP BY c.customer_id, c.full_nameORDER BY c.customer_id;COUNT(*) would count the NULL-extended row and report one for Lina. COUNT(s.sale_id) counts only actual matched child rows.
RIGHT JOIN is a readability choice
SELECT p.product_id, p.product_name, s.sale_idFROM sale AS sRIGHT JOIN product AS p ON p.product_id = s.product_idORDER BY p.product_id, s.sale_id;The same relationship can be written by swapping inputs and using LEFT JOIN. Many teams standardize on LEFT JOIN for readability and broader compatibility.
SELECT p.product_id, p.product_name, s.sale_idFROM product AS pLEFT JOIN sale AS s ON s.product_id = p.product_idORDER BY p.product_id, s.sale_id;FULL OUTER JOIN for reconciliation
SELECT COALESCE(ws.warehouse_code, rt.warehouse_code) AS warehouse_code, COALESCE(ws.product_id, rt.product_id) AS product_id, ws.stock_qty, rt.target_qty, CASE WHEN ws.product_id IS NULL THEN 'target only' WHEN rt.product_id IS NULL THEN 'stock only' ELSE 'both' END AS reconciliation_stateFROM warehouse_stock AS wsFULL OUTER JOIN reorder_target AS rt ON rt.warehouse_code = ws.warehouse_code AND rt.product_id = ws.product_idORDER BY warehouse_code, product_id;COALESCE publishes the available key from either side. The state column makes unmatched cases explicit.
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
- List every product and any associated sales.
- Find products that have never been sold.
- Count sales per product while retaining zero-sale products.
- Compare filtering a channel in ON versus WHERE.
- Reconcile warehouse stock and reorder targets with a full join.
SELECT p.product_id, p.product_name, COUNT(s.sale_id) AS sale_count, COALESCE(SUM(s.quantity), 0) AS units_soldFROM product AS pLEFT JOIN sale AS s ON s.product_id = p.product_idGROUP BY p.product_id, p.product_nameORDER BY p.product_id;Checkpoint
Preservation and filtering
- Which side is preserved by LEFT JOIN?
- Why can a right-table predicate in WHERE remove unmatched left rows?
- Why is COUNT(child_primary_key) safer than COUNT(*)?
- When is FULL OUTER JOIN useful?
Review the answers
LEFT preserves its left input. WHERE retains only TRUE and rejects NULL-extended comparisons. Counting the child key ignores synthetic rows. FULL joins are useful for reconciliation and bidirectional missingness.
Summary and references
- Outer joins preserve unmatched rows deliberately.
- ON controls matching; WHERE controls final row retention.
- NULL-extended rows represent absence, not stored child records.
- Count a non-nullable child key to report zero correctly.
- FULL OUTER JOIN exposes rows found on only one side.