Chapter 07 · Joining Related Tables
INNER JOIN and Equi-Joins
Use explicit equi-joins to connect related rows while keeping relationship logic separate from business filtering.
Learning outcomes
INNER JOIN returns matched row pairs only. Most introductory joins are equi-joins because key values are compared with equality, but correctness still depends on choosing the complete relationship and qualifying every ambiguous column.
Write explicit INNER JOIN queries with clear aliases and qualified columns.
Chain several one-to-many and many-to-one relationships safely.
Separate relationship predicates in ON from row filters in WHERE.
Use USING only when its name-based behavior is intentional.
INNER JOIN and equi-join are related concepts
| Concept | Definition | Example |
|---|---|---|
| INNER JOIN | A join type that discards unmatched rows | sale JOIN customer |
| Equi-join | A join whose matching condition uses equality | c.customer_id = s.customer_id |
| Theta join | A join using any comparison predicate | Amount inside a price interval |
| Natural join | Name-based equality inferred by the engine | Avoid unless the schema contract is tightly controlled |
An inner join can use inequalities; an equi-join can be inner or outer. Keep join type and predicate type conceptually separate.
Qualify columns and use role-based aliases
SELECT s.sale_id, c.full_name AS customer_name, p.sku, p.product_name, s.quantityFROM sale AS sINNER JOIN customer AS c ON c.customer_id = s.customer_idINNER JOIN product AS p ON p.product_id = s.product_idORDER BY s.sale_id;Short aliases reduce noise, but they should communicate table roles. Qualifying keys prevents ambiguity and makes code review easier.
Relationship logic belongs in ON
Keeping these stages visible helps reviewers distinguish “how rows relate” from “which matched rows the report needs.”
SELECT s.sale_id, c.full_name, p.product_name, s.channelFROM sale AS sJOIN customer AS c ON c.customer_id = s.customer_idJOIN product AS p ON p.product_id = s.product_idWHERE c.segment = 'business' AND s.channel IN ('direct', 'partner')ORDER BY s.sale_id;Moving these inner-join filters into ON often produces the same rows, but it mixes two different responsibilities. The distinction becomes semantically critical for outer joins.
USING can remove duplicate key output
SELECT sale_id, customer_id, full_name, sold_atFROM saleJOIN customer USING (customer_id)ORDER BY sale_id;USING (customer_id) creates an equality condition and exposes one merged key column. It is concise, but renaming a column or adding same-named columns can change maintainability. ON is more explicit and supports differently named keys.
Join order versus logical meaning
For inner joins, the optimizer may reorder inputs while preserving semantics. Write the query in a human-readable relationship order and provide indexes on frequently matched keys; do not assume textual order is the physical execution order.
EXPLAIN QUERY PLANSELECT s.sale_id, c.full_name, p.product_nameFROM sale AS sJOIN customer AS c ON c.customer_id = s.customer_idJOIN product AS p ON p.product_id = s.product_idWHERE s.sale_id >= 102;Composite relationships require every component
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;Joining only on product_id would combine rows from different warehouses. A composite key represents one relationship; omitting a component changes the relationship.
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
- Return each sale with customer, product, quantity, and gross amount.
- Filter for course products sold to business customers.
- Rewrite one key equality with
USINGand compare the result columns. - Join stock to reorder targets using the complete composite key.
- Use
EXPLAIN QUERY PLANand identify primary-key lookups.
SELECT s.sale_id, c.full_name AS customer_name, p.product_name, 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_idWHERE c.segment = 'business' AND p.category = 'course'ORDER BY s.sale_id;Common failures
Ambiguous columns
Unqualified IDs and names become unreadable or raise errors.
Partial composite predicate
Rows match across the wrong warehouse, tenant, version, or date.
Accidental many-to-many
Two nonunique inputs multiply rows even though the query “looks right.”
Blind NATURAL JOIN
A future same-named column silently becomes part of the predicate.
Checkpoint
Review the relationship
- Can an INNER JOIN use a non-equality predicate?
- Why should relationship predicates normally remain in ON?
- What does USING change in the projected key columns?
- Why must both warehouse_code and product_id appear in the composite join?
Review the answers
INNER describes preservation, not the comparison operator. ON communicates relationship logic. USING merges the named key in the output. Both components identify one warehouse-product fact.
Summary and references
- INNER JOIN keeps only matched row pairs.
- Equi-joins compare relationship values with equality.
- Aliases and qualification make multi-table queries auditable.
- ON defines relationships; WHERE filters the joined result.
- Composite relationships require complete predicates.