Chapter 05 · SQL Querying: Joins, Subqueries, CTEs, Set Operations, and LATERAL
Join Types, Join Conditions, Cardinality Reasoning, and Join Elimination Concepts
Predict PostgreSQL join results before tuning them: inner and outer joins, USING/NATURAL/CROSS forms, semi/anti-join patterns, cardinality multiplication, and constraint-informed planner opportunities.
Learning outcomes
A ServiceHub report must show every work order, its customer, assigned technician when one exists, and selected tags. The first join “works” but returns 14 rows from 10 work orders. A second version unexpectedly loses the unassigned work order. This is the central join skill: predict row cardinality and null-extension from the relational model before interpreting any plan.
Predict row counts for inner, outer, and cross joins from keys, uniqueness, optionality, and join predicates.
Distinguish predicates in ON from predicates in
WHERE, especially for outer joins.
Use USING intentionally and recognize why
NATURAL joins are fragile under schema
evolution.
Express existence and non-existence as semi-join/anti-join patterns without accidental row multiplication.
Connect declared uniqueness/foreign keys to planner opportunities while treating join removal as an optimization, not a semantic promise.
1. Chapter 05 lab bootstrap
All five lessons use the same small
ServiceHub dataset so row counts and edge cases
remain explainable. Run this as
servicehub_owner (or another role that owns the
disposable app schema) in
servicehub_lab. The objects are deliberately
prefixed ch05_. Before the drops, verify the
current database and inspect the matching objects; do not paste
this into a production database.
The DROP statements below target only chapter-scoped objects. If your database contains valuable objects with these names, stop and use another disposable database. No CASCADE is used.
\echo 'Verify target before recreating chapter-scoped lab objects'SELECT current_database(), current_user;\dt app.ch05_*DROP TABLE IF EXISTS app.ch05_work_order_event;DROP TABLE IF EXISTS app.ch05_work_order_tag;DROP TABLE IF EXISTS app.ch05_work_order;DROP TABLE IF EXISTS app.ch05_technician;DROP TABLE IF EXISTS app.ch05_customer;DROP TABLE IF EXISTS app.ch05_team;CREATE TABLE app.ch05_customer ( customer_id integer PRIMARY KEY, customer_name text NOT NULL, region text, active boolean NOT NULL DEFAULT true);CREATE TABLE app.ch05_technician ( technician_id integer PRIMARY KEY, technician_name text NOT NULL, specialty text NOT NULL, team_code text NOT NULL);CREATE TABLE app.ch05_work_order ( work_order_id integer PRIMARY KEY, customer_id integer NOT NULL REFERENCES app.ch05_customer(customer_id), assigned_technician_id integer REFERENCES app.ch05_technician(technician_id), status text NOT NULL CHECK (status IN ('queued','open','closed','cancelled')), priority smallint NOT NULL CHECK (priority BETWEEN 1 AND 4), city text NOT NULL, opened_at timestamptz NOT NULL, closed_at timestamptz, estimated_minutes integer NOT NULL CHECK (estimated_minutes >= 0), actual_minutes integer CHECK (actual_minutes >= 0), cost numeric(10,2) NOT NULL CHECK (cost >= 0));CREATE TABLE app.ch05_work_order_tag ( work_order_id integer NOT NULL REFERENCES app.ch05_work_order(work_order_id), tag text NOT NULL, PRIMARY KEY (work_order_id, tag));CREATE TABLE app.ch05_work_order_event ( event_id integer PRIMARY KEY, work_order_id integer NOT NULL REFERENCES app.ch05_work_order(work_order_id), event_at timestamptz NOT NULL, event_type text NOT NULL, detail text);CREATE TABLE app.ch05_team ( team_id integer PRIMARY KEY, team_name text NOT NULL UNIQUE, parent_team_id integer REFERENCES app.ch05_team(team_id));INSERT INTO app.ch05_customer VALUES(1,'Northwind Health','North',true),(2,'Alpine Manufacturing','North',true),(3,'City Library','Central',true),(4,'Delta Foods','South',true),(5,'Evergreen School','South',false);INSERT INTO app.ch05_technician VALUES(10,'Ada','electrical','A'),(11,'Linus','network','A'),(12,'Grace','mechanical','B'),(13,'Ken','network','B');INSERT INTO app.ch05_work_order VALUES(1001,1,10,'closed',1,'Baku','2026-08-01 08:00+00','2026-08-01 10:00+00',90,110,240.00),(1002,1,11,'open',2,'Baku','2026-08-02 09:00+00',NULL,60,NULL,80.00),(1003,2,10,'closed',2,'Sumqayit','2026-08-02 07:30+00','2026-08-02 09:00+00',120,85,150.00),(1004,2,NULL,'queued',3,'Sumqayit','2026-08-03 12:00+00',NULL,45,NULL,50.00),(1005,3,12,'closed',1,'Baku','2026-08-01 06:00+00','2026-08-01 08:30+00',120,150,300.00),(1006,4,13,'cancelled',4,'Ganja','2026-08-04 14:00+00','2026-08-04 14:10+00',30,10,0.00),(1007,4,13,'open',2,'Ganja','2026-08-05 11:00+00',NULL,90,NULL,120.00),(1008,1,10,'closed',1,'Baku','2026-08-06 05:00+00','2026-08-06 06:00+00',60,55,90.00),(1009,3,12,'closed',2,'Baku','2026-08-06 09:00+00','2026-08-06 12:00+00',90,180,400.00),(1010,2,11,'open',1,'Baku','2026-08-07 10:00+00',NULL,0,NULL,75.00);INSERT INTO app.ch05_work_order_tag VALUES(1001,'electrical'),(1001,'urgent'),(1002,'network'),(1003,'electrical'),(1005,'mechanical'),(1005,'safety'),(1007,'network'),(1007,'urgent'),(1008,'electrical'),(1009,'mechanical'),(1009,'urgent'),(1010,'network');INSERT INTO app.ch05_work_order_event VALUES(1,1001,'2026-08-01 08:00+00','created','portal'),(2,1001,'2026-08-01 09:30+00','arrived','Ada on site'),(3,1001,'2026-08-01 10:00+00','closed','restored'),(4,1002,'2026-08-02 09:00+00','created','portal'),(5,1002,'2026-08-02 09:10+00','assigned','Linus'),(6,1003,'2026-08-02 07:30+00','created','monitoring'),(7,1003,'2026-08-02 09:00+00','closed','breaker replaced'),(8,1007,'2026-08-05 11:00+00','created','phone'),(9,1007,'2026-08-05 11:20+00','assigned','Ken'),(10,1008,'2026-08-06 05:00+00','created','monitoring'),(11,1008,'2026-08-06 06:00+00','closed','reset'),(12,1009,'2026-08-06 09:00+00','created','portal'),(13,1009,'2026-08-06 10:00+00','diagnosed','pump wear'),(14,1009,'2026-08-06 12:00+00','closed','pump replaced'),(15,1010,'2026-08-07 10:00+00','created','portal');INSERT INTO app.ch05_team VALUES(1,'Field Operations',NULL),(2,'Electrical',1),(3,'Network',1),(4,'Mechanical',1),(5,'Night Shift',3);
After setup, app.ch05_work_order contains 10 work
orders, including one unassigned row, several NULL completion
values, and one zero-minute estimate. Those details are
intentional: they make outer joins, three-valued logic,
NOT IN, scalar errors, and evaluation-order
mistakes observable.
2. Cardinality first: one row can become many
A join combines rows that satisfy its join condition. If one work order matches one customer, the relationship is many-to-one and does not multiply that work order. If one work order matches several tags, joining tags creates one result row per matching tag. That multiplication is correct relational behavior—not a duplicate-removal problem.
SELECT count(*) AS base_rows FROM app.ch05_work_order;SELECT count(*) AS with_customerFROM app.ch05_work_order wJOIN app.ch05_customer c ON c.customer_id = w.customer_id;SELECT count(*) AS with_tagsFROM app.ch05_work_order wJOIN app.ch05_work_order_tag t ON t.work_order_id = w.work_order_id;base_rows | 10with_customer | 10with_tags | 12
The customer join remains at 10 because every work order has
exactly one referenced customer. The tag join returns 12 because
only tagged work orders participate and some have two tags.
Adding DISTINCT to “fix duplicates” would hide the
modeling fact and can corrupt aggregates.
3. Inner and outer joins answer different questions
An inner join keeps only matching pairs. A left outer join also emits each unmatched left row once, filling the right-side columns with NULL. In this dataset, work order 1004 has no assigned technician.
SELECT w.work_order_id, w.assigned_technician_id, t.technician_nameFROM app.ch05_work_order AS wLEFT JOIN app.ch05_technician AS t ON t.technician_id = w.assigned_technician_idWHERE w.work_order_id IN (1003,1004)ORDER BY w.work_order_id;work_order_id | assigned_technician_id | technician_name--------------+------------------------+----------------1003 | 10 | Ada1004 | NULL | NULL
RIGHT JOIN and FULL JOIN are available
too. They are useful when the business question genuinely needs
unmatched rows from the right side or both sides. Do not choose
a join type based on perceived performance; choose it from
required row-preservation semantics.
4. ON versus WHERE can change outer-join meaning
The join condition decides which right-side rows count as matches. A later WHERE condition filters the already-joined result. If the WHERE condition requires a right-side value, NULL-extended rows fail that filter and disappear.
-- Requirement: show every work order; show technician only when specialty is network.-- Wrong: unassigned/non-network rows are filtered away.SELECT w.work_order_id, t.technician_nameFROM app.ch05_work_order wLEFT JOIN app.ch05_technician t ON t.technician_id = w.assigned_technician_idWHERE t.specialty = 'network'ORDER BY w.work_order_id;-- Correct for the stated requirement: specialty is part of matching.SELECT w.work_order_id, t.technician_nameFROM app.ch05_work_order wLEFT JOIN app.ch05_technician t ON t.technician_id = w.assigned_technician_id AND t.specialty = 'network'ORDER BY w.work_order_id;The repaired query returns all 10 work orders. Technician names appear only for work assigned to Linus or Ken. This is a correctness change, not a tuning trick.
5. USING is concise; NATURAL couples queries to future column names
USING (customer_id) is shorthand for equality of
same-named columns and emits one merged join column instead of
both copies. That can be convenient when the shared name
represents the same key by design.
SELECT customer_id, c.customer_name, w.work_order_idFROM app.ch05_customer cJOIN app.ch05_work_order w USING (customer_id)WHERE customer_id = 1ORDER BY w.work_order_id;customer_id | customer_name | work_order_id------------+-------------------+--------------1 | Northwind Health | 10011 | Northwind Health | 10021 | Northwind Health | 1008
NATURAL JOIN automatically uses
all same-named columns. A future migration that adds
another shared column can silently change the join predicate.
For durable application/reporting SQL, prefer explicit
ON or an explicit USING list so schema
evolution cannot redefine the query by coincidence.
6. Cross joins are deliberate Cartesian products
CROSS JOIN pairs every left row with every right
row. That is useful for generating matrices—for example every
region crossed with every priority—but disastrous when a join
predicate was accidentally omitted.
WITH regions(region) AS (VALUES ('North'),('Central'),('South')), priorities(priority) AS (VALUES (1),(2),(3),(4))SELECT region, priorityFROM regionsCROSS JOIN prioritiesORDER BY region, priority;The result has 3 × 4 = 12 rows. Before approving any cross join against large tables, compute the cardinality envelope explicitly.
7. Semi-joins and anti-joins express existence without multiplication
If the question is “which work orders have an urgent tag?”, you
do not need the tag row in the output.
EXISTS expresses that membership test directly and
returns each qualifying work order once, regardless of how many
matching child rows exist.
SELECT w.work_order_idFROM app.ch05_work_order wWHERE EXISTS ( SELECT 1 FROM app.ch05_work_order_tag t WHERE t.work_order_id = w.work_order_id AND t.tag = 'urgent')ORDER BY w.work_order_id;SELECT c.customer_id, c.customer_nameFROM app.ch05_customer cWHERE NOT EXISTS ( SELECT 1 FROM app.ch05_work_order w WHERE w.customer_id = c.customer_id)ORDER BY c.customer_id;urgent work orders: 1001, 1007, 1009customer with no work orders: 5 | Evergreen SchoolThe planner may implement these with explicit semi/anti join nodes or another equivalent strategy. The application contract is existence/non-existence, not a particular node name.
8. Deliberately wrong aggregate: count after joining a many-valued child
Suppose you need work-order count and total cost per customer. Joining tags first and then aggregating counts/costs repeats a work order once per tag. Work order 1001 costs 240 and has two tags, so it contributes 480 to the joined sum.
SELECT c.customer_name, count(w.work_order_id) AS joined_rows, sum(w.cost) AS wrong_costFROM app.ch05_customer cJOIN app.ch05_work_order w ON w.customer_id = c.customer_idLEFT JOIN app.ch05_work_order_tag t ON t.work_order_id = w.work_order_idWHERE c.customer_id = 1GROUP BY c.customer_name;customer_name | joined_rows | wrong_cost-----------------+-------------+-----------Northwind Health | 4 | 650.00Customer 1 actually has three work orders costing 240 + 80 + 90 = 410. Repair by aggregating the work-order relation before joining tags, using EXISTS when only tag existence matters, or aggregating tags separately and joining the aggregated relation.
SELECT c.customer_name, count(*) AS work_orders, sum(w.cost) AS total_costFROM app.ch05_customer cJOIN app.ch05_work_order w ON w.customer_id = c.customer_idWHERE c.customer_id = 1GROUP BY c.customer_name;customer_name | work_orders | total_cost-----------------+-------------+-----------Northwind Health | 3 | 410.009. Constraints can create planner opportunities, not contractual plans
Primary keys, UNIQUE constraints, foreign keys, and nullability tell PostgreSQL facts about cardinality and legal states. Those facts can support selectivity estimates, transformations, and in some cases removal of a redundant outer join. But “the planner will eliminate this join” should never be part of application correctness.
EXPLAIN (COSTS ON)SELECT w.work_order_idFROM app.ch05_work_order wLEFT JOIN app.ch05_customer c ON c.customer_id = w.customer_id;-- Then compare with:EXPLAIN (COSTS ON)SELECT w.work_order_idFROM app.ch05_work_order w;On a given PostgreSQL build and schema state, the plans may become equivalent because the right relation is unique and unused. That is an optimizer opportunity. Changing constraints, selected columns, predicates, or PostgreSQL version can change the chosen plan without changing SQL semantics.
10. Hands-on lab: write the expected row count first
-- A. Predict before executing: how many rows?SELECT w.work_order_id, t.tagFROM app.ch05_work_order wLEFT JOIN app.ch05_work_order_tag t ON t.work_order_id = w.work_order_idORDER BY w.work_order_id, t.tag;-- B. Customers with at least one closed work order, exactly once each.SELECT c.customer_id, c.customer_nameFROM app.ch05_customer cWHERE EXISTS ( SELECT 1 FROM app.ch05_work_order w WHERE w.customer_id = c.customer_id AND w.status = 'closed')ORDER BY c.customer_id;-- C. Keep all work orders; attach only network technicians.SELECT w.work_order_id, t.technician_nameFROM app.ch05_work_order wLEFT JOIN app.ch05_technician t ON t.technician_id = w.assigned_technician_id AND t.specialty = 'network'ORDER BY w.work_order_id;Check your understanding
- Why does joining tags increase row count even when no data is duplicated incorrectly?
- How can a predicate moved from ON to WHERE change a LEFT JOIN result?
- Why is NATURAL JOIN fragile in long-lived application SQL?
- When is EXISTS preferable to joining a child table?
- Why should join elimination be treated as an optimization rather than part of correctness?
Review the answers
A one-to-many relationship legitimately produces one joined row per child. WHERE filtering occurs after null-extension, so a right-side predicate can remove unmatched left rows. NATURAL JOIN changes when new same-named columns appear. EXISTS states membership without multiplying the parent. Planner transformations depend on constraints, query shape, and version; the SQL result must remain correct whether or not a join is removed.
11. Production judgment and bridge
Join correctness is cardinality engineering. Keep key/uniqueness
assumptions declared, isolate predicates at the right semantic
boundary, and aggregate at the grain you intend. The next lesson
uses subqueries when a value or relation must be derived inside
another query and introduces LATERAL for the
important case where that derived relation depends on the
current outer row.