Chapter 07 · Joining Related Tables
CROSS JOIN, Self-Joins, and Non-Equi Joins
Not every relationship is a foreign-key equality: some joins create combinations, traverse one table twice, or match intervals.
Learning outcomes
Equality between separate key columns is common, but SQL joins also generate all combinations, compare rows within one table, and match values to intervals or temporal ranges.
01
Use CROSS JOIN intentionally and estimate its row count.
02
Use self-joins to navigate parent-child relationships in one table.
03
Write non-equi joins with exclusive interval boundaries.
04
Prevent uncontrolled row multiplication and overlapping range matches.
CROSS JOIN creates combinations
sqlite · every segment with every sales channel
WITH segment(segment_name) AS ( VALUES ('consumer'), ('business')),channel(channel_name) AS ( VALUES ('web'), ('partner'), ('direct'))SELECT segment_name, channel_nameFROM segmentCROSS JOIN channelORDER BY segment_name, channel_name;Two segments multiplied by three channels produce six combinations. CROSS JOIN is useful for grids, test cases, calendars, and completeness checks.
sqlite · create the expected grid and attach actual counts
WITH segment(segment_name) AS ( VALUES ('consumer'), ('business')),channel(channel_name) AS ( VALUES ('web'), ('partner'), ('direct')),actual AS ( SELECT c.segment, s.channel, COUNT(*) AS sale_count FROM sale AS s JOIN customer AS c ON c.customer_id = s.customer_id GROUP BY c.segment, s.channel)SELECT sg.segment_name, ch.channel_name, COALESCE(a.sale_count, 0) AS sale_countFROM segment AS sgCROSS JOIN channel AS chLEFT JOIN actual AS a ON a.segment = sg.segment_name AND a.channel = ch.channel_nameORDER BY sg.segment_name, ch.channel_name;This verbose form emphasizes the pattern: generate expected combinations, then outer join actual observations.
Self-join: one table, two roles
employee alias e
manager_id relationship
employee alias m
manager_name projection
A self-join does not duplicate the table. Two aliases give the same table two logical roles in one query.
sqlite · employee and optional manager
SELECT e.employee_id, e.employee_name, m.employee_name AS manager_nameFROM employee AS eLEFT JOIN employee AS m ON m.employee_id = e.manager_idORDER BY e.employee_id;The LEFT JOIN preserves the top-level director whose manager reference is NULL.
sqlite · employees who report to the same manager
SELECT a.employee_name AS employee_a, b.employee_name AS employee_b, a.manager_idFROM employee AS aJOIN employee AS b ON b.manager_id = a.manager_id AND b.employee_id > a.employee_idWHERE a.manager_id IS NOT NULLORDER BY a.manager_id, a.employee_id, b.employee_id;The strict ID comparison avoids pairing each employee with themselves and avoids returning both A–B and B–A.
Non-equi join: match an amount to a band
sqlite · half-open price intervals
WITH sale_amount AS ( SELECT s.sale_id, p.unit_price * s.quantity AS gross_amount FROM sale AS s JOIN product AS p ON p.product_id = s.product_id)SELECT sa.sale_id, ROUND(sa.gross_amount, 2) AS gross_amount, pb.band_nameFROM sale_amount AS saJOIN price_band AS pb ON sa.gross_amount >= pb.minimum_amount AND ( sa.gross_amount < pb.maximum_amount OR pb.maximum_amount IS NULL )ORDER BY sa.sale_id;Half-open intervals [minimum, maximum) prevent boundary values from matching two adjacent bands. The final unbounded band uses NULL as “no upper limit.”
Range integrity is a data-design problem
| Risk | Symptom | Control |
|---|---|---|
| Overlapping bands | One fact matches several ranges | Validate non-overlap during data changes. |
| Gap between bands | A fact matches no range | Define complete coverage or handle unmatched rows. |
| Inclusive upper and lower bounds | Boundary value matches twice | Use half-open intervals consistently. |
| NULL boundary without explicit logic | UNKNOWN predicate removes expected match | Write the open-ended condition directly. |
Non-equi temporal join pattern
portable pattern · effective-dated rule
SELECT event.event_id, rule.rule_idFROM eventJOIN effective_rule AS rule ON event.occurred_at >= rule.valid_from AND ( event.occurred_at < rule.valid_to OR rule.valid_to IS NULL );The same half-open interval design works for price lists, exchange rates, employee assignments, and slowly changing dimensions. The tables are illustrative; they are not part of the chapter setup.
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.
sqlite · chapter07_setup.sql
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');Relationship map
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
- Generate every customer segment and channel combination.
- Attach actual sale counts and retain zero-count combinations.
- Return each employee with an optional manager.
- Find pairs of coworkers without duplicate mirrored pairs.
- Classify each sale amount into exactly one price band.
sqlite · simpler expected-grid solution
WITH segment(segment_name) AS ( VALUES ('consumer'), ('business')),channel(channel_name) AS ( VALUES ('web'), ('partner'), ('direct')),expected AS ( SELECT segment.segment_name, channel.channel_name FROM segment CROSS JOIN channel),actual AS ( SELECT c.segment AS segment_name, s.channel AS channel_name, COUNT(*) AS sale_count FROM sale AS s JOIN customer AS c ON c.customer_id = s.customer_id GROUP BY c.segment, s.channel)SELECT e.segment_name, e.channel_name, COALESCE(a.sale_count, 0) AS sale_countFROM expected AS eLEFT JOIN actual AS a ON a.segment_name = e.segment_name AND a.channel_name = e.channel_nameORDER BY e.segment_name, e.channel_name;Checkpoint
Choose the join form
- What is the expected row count of a CROSS JOIN between 4 and 7 rows?
- Why are two aliases required in a self-join?
- Why use a half-open interval for adjacent bands?
- How can an open-ended upper range be represented?
Review the answers
The product is 28. Aliases assign distinct logical roles. Half-open intervals prevent double matches at boundaries. A NULL upper bound plus explicit OR logic can represent infinity.
Summary and references
- CROSS JOIN intentionally forms every combination.
- Self-joins assign two or more roles to one table.
- Non-equi joins match ranges, dates, and inequalities.
- Half-open intervals prevent boundary duplication.
- Range overlap and gaps must be governed as data integrity concerns.