Chapter 05 · SQL Querying: Joins, Subqueries, CTEs, Set Operations, and LATERAL
UNION / INTERSECT / EXCEPT, DISTINCT ON, Row Constructors, and Query Composition
Compose PostgreSQL result sets with duplicate-aware set operations, deterministic DISTINCT ON, row constructors, type alignment, and layered query boundaries that remain correct and maintainable.
Learning outcomes
ServiceHub's reporting layer now needs to combine several independently correct relations: merge work queues, find overlap between tagged categories, subtract already-closed items, choose the latest event per work order, and compare compound keys for keyset pagination. PostgreSQL provides direct operators for each job, but duplicate semantics, type alignment, precedence, and ordering rules are part of the contract.
Use UNION/UNION ALL,
INTERSECT/INTERSECT ALL, and
EXCEPT/EXCEPT ALL with deliberate
duplicate semantics.
Resolve compatible output types and understand where ORDER BY applies in set-operation queries.
Use PostgreSQL-specific DISTINCT ON only with a
deterministic leftmost ordering contract.
Compare row constructors lexicographically while accounting for NULL behavior.
Build layered queries whose intermediate relations have explicit grain and meaning.
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. Set operations combine whole result relations
All branches of a set operation must produce the same number of
columns, and corresponding columns must resolve to compatible
types. UNION, INTERSECT, and
EXCEPT remove duplicates by default. Their
ALL variants preserve multiplicity according to the
operation.
| Operator | Without ALL | With ALL |
|---|---|---|
UNION |
one copy of each distinct row | concatenate all copies |
INTERSECT |
rows present on both sides, one copy | min(left_count,right_count) copies |
EXCEPT |
distinct rows from left not present on right | max(left_count-right_count,0) copies |
SELECT city FROM app.ch05_work_order WHERE priority = 1UNIONSELECT city FROM app.ch05_work_order WHERE status = 'open'ORDER BY city;SELECT city FROM app.ch05_work_order WHERE priority = 1UNION ALLSELECT city FROM app.ch05_work_order WHERE status = 'open'ORDER BY city;
The first query returns each city once. The second keeps every
occurrence from both branches, including a work order that
satisfies both branch predicates. Use ALL when
multiplicity is part of the relation or when you know branches
are disjoint; do not pay for deduplication accidentally.
3. Precedence and parentheses matter
INTERSECT binds more tightly than
UNION and EXCEPT. Multiple UNION or
EXCEPT operations otherwise associate left-to-right unless
parentheses change the grouping. Complex reporting SQL should
use parentheses when human readers might otherwise need to
remember precedence rules.
(SELECT work_order_id FROM app.ch05_work_order WHERE city='Baku')UNION( (SELECT work_order_id FROM app.ch05_work_order WHERE priority=1) INTERSECT (SELECT work_order_id FROM app.ch05_work_order WHERE status='closed'))ORDER BY work_order_id;An ORDER BY attached to the combined result can normally reference output column names or ordinal positions, not an arbitrary expression. To order a branch before LIMIT, parenthesize that branch and make the branch-local ORDER BY part of the subquery.
4. Type alignment is resolved across branches
PostgreSQL must choose one output type for each set-operation column. Unknown literals can be coerced to match a typed branch, but incompatible content can then fail during conversion.
SELECT 1 AS codeUNION ALLSELECT '2';-- This attempts to resolve the unknown literal as integer and fails:SELECT 1 AS codeUNION ALLSELECT 'external';-- Explicit text contract:SELECT 1::text AS codeUNION ALLSELECT 'external'::text;In interfaces, ETL, and migration SQL, explicit casts make the union contract reviewable instead of letting an added branch silently change type resolution.
5. DISTINCT ON is PostgreSQL-specific “first row per group”
DISTINCT ON (key) keeps the first row for each
equal key according to the incoming sort order. Without a
sufficient ORDER BY, “first” is unpredictable. PostgreSQL also
requires the DISTINCT ON expressions to match the leftmost ORDER
BY expressions.
SELECT DISTINCT ON (e.work_order_id) e.work_order_id, e.event_type, e.event_at, e.event_idFROM app.ch05_work_order_event eORDER BY e.work_order_id, e.event_at DESC, e.event_id DESC;
The unique event_id DESC final key resolves ties in
event_at. If the presentation needs “latest events
globally newest first,” wrap this query and order the outer
result by event time; the inner ORDER BY exists to choose the
winner per work order.
SELECT *FROM ( SELECT DISTINCT ON (e.work_order_id) e.work_order_id, e.event_type, e.event_at, e.event_id FROM app.ch05_work_order_event e ORDER BY e.work_order_id, e.event_at DESC, e.event_id DESC) latestORDER BY latest.event_at DESC, latest.work_order_id DESC;6. Deliberately wrong DISTINCT ON ordering
SELECT DISTINCT ON (work_order_id) work_order_id, event_type, event_atFROM app.ch05_work_order_eventORDER BY event_at DESC;ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
Repair the query by beginning ORDER BY with
work_order_id, then append the row-preference keys.
This rule is not cosmetic: it makes the grouping key and
winning-row order coherent.
7. Row constructors compare compound values lexicographically
A row constructor groups expressions into one row value. For
<, <=, >, and
>=, PostgreSQL compares fields left-to-right and
stops at the first unequal or null pair. If that deciding pair
contains NULL, the comparison is UNKNOWN.
SELECT ROW(2,10) > ROW(1,999) AS first_field_decides, ROW(1,20) > ROW(1,10) AS second_field_decides, ROW(1,NULL) > ROW(1,10) AS null_decision, ROW(1,NULL) IS DISTINCT FROM ROW(1,NULL) AS null_safe_difference;first_field_decides | second_field_decides | null_decision | null_safe_difference--------------------+----------------------+---------------+---------------------t | t | NULL | fThis is useful for keyset pagination with a deterministic compound order. If a pagination key can be NULL, define a null policy explicitly; do not assume row comparison magically normalizes it.
SELECT work_order_id, opened_atFROM app.ch05_work_orderWHERE (opened_at, work_order_id) < (TIMESTAMPTZ '2026-08-06 09:00+00', 1009)ORDER BY opened_at DESC, work_order_id DESCLIMIT 3;8. INTERSECT and EXCEPT can express set questions directly
-- Work orders tagged urgent AND currently open.SELECT work_order_idFROM app.ch05_work_order_tagWHERE tag='urgent'INTERSECTSELECT work_order_idFROM app.ch05_work_orderWHERE status='open'ORDER BY work_order_id;-- Customers with any work order EXCEPT customers with closed work.SELECT customer_id FROM app.ch05_work_orderEXCEPTSELECT customer_id FROM app.ch05_work_order WHERE status='closed'ORDER BY customer_id;urgent + open: 1007customers with work but no closed work: 4An EXISTS/NOT EXISTS query could express the same business idea. Choose the form that makes the relation easiest to verify and maintain; then measure if performance matters.
9. Composition pattern: establish grain at every layer
Large queries become maintainable when each layer has an explicit grain: one row per event, one latest event per work order, one work-order row enriched with customer, then one final presentation ordering. CTEs, subqueries, and set operations are tools for enforcing those boundaries, not goals by themselves.
WITH latest_event AS ( SELECT DISTINCT ON (work_order_id) work_order_id, event_type, event_at FROM app.ch05_work_order_event ORDER BY work_order_id, event_at DESC, event_id DESC),active_work AS ( SELECT w.work_order_id, w.customer_id, w.priority, w.opened_at FROM app.ch05_work_order w WHERE w.status IN ('queued','open'))SELECT a.work_order_id, c.customer_name, a.priority, l.event_type AS latest_event, l.event_at AS latest_event_atFROM active_work aJOIN app.ch05_customer c USING (customer_id)LEFT JOIN latest_event l USING (work_order_id)ORDER BY a.priority, a.opened_at, a.work_order_id;10. Hands-on lab: compose without hiding semantics
-- A. UNION ALL a queue from two explicit sources and preserve source label.SELECT 'open'::text AS source, work_order_idFROM app.ch05_work_order WHERE status='open'UNION ALLSELECT 'queued'::text AS source, work_order_idFROM app.ch05_work_order WHERE status='queued'ORDER BY source, work_order_id;-- B. Latest event per work order, deterministic.SELECT DISTINCT ON (work_order_id) work_order_id, event_type, event_at, event_idFROM app.ch05_work_order_eventORDER BY work_order_id, event_at DESC, event_id DESC;-- C. Compound row comparison.SELECT work_order_id, opened_atFROM app.ch05_work_orderWHERE (opened_at, work_order_id) > (TIMESTAMPTZ '2026-08-05 11:00+00', 1007)ORDER BY opened_at, work_order_id;Check your understanding
- What duplicate behavior differs between UNION and UNION ALL?
- Why can INTERSECT bind differently from UNION in an unparenthesized expression?
- What ordering rule makes DISTINCT ON deterministic?
- Why does a row comparison involving a NULL deciding field return UNKNOWN?
- Why is explicit type casting valuable in long-lived set-operation queries?
Review the answers
UNION removes duplicates while UNION ALL preserves every copy. INTERSECT has higher precedence than UNION/EXCEPT, so parentheses improve clarity. DISTINCT ON needs ORDER BY beginning with the DISTINCT ON keys plus deterministic winner keys. Lexicographic row comparison becomes UNKNOWN if the first deciding pair contains NULL. Explicit casts document and stabilize the common output type across branches.
11. Production judgment and chapter bridge
PostgreSQL query fluency is the ability to state relational
intent precisely before asking the optimizer to make it fast.
Across this chapter you used NULL-aware predicates,
deterministic ordering, cardinality-aware joins, subquery shape
contracts, LATERAL dependencies, recursive CTE termination,
set-operation multiplicity, and DISTINCT ON winner ordering.
Chapter 06 builds on these foundations with aggregates, window
functions, grouping sets, INSERT ... ON CONFLICT,
MERGE, and RETURNING.