Chapter 05 · SQL Querying: Joins, Subqueries, CTEs, Set Operations, and LATERAL
Subqueries, EXISTS, IN, Scalar Contexts, Correlation, and LATERAL
Use scalar, row, table, and correlated subqueries safely; understand EXISTS and IN/NOT IN NULL semantics; and apply LATERAL for per-row derived relations without confusing correctness with plan shape.
Learning outcomes
ServiceHub needs three derived facts in one report: whether each
customer has open work, the most recent event for each work
order, and whether a technician belongs to a candidate set.
These look like “queries inside queries,” but the shape matters:
a scalar context demands at most one row,
EXISTS asks only about existence, and
LATERAL lets a derived table depend on the current
row from an earlier FROM item.
Distinguish scalar, row, table, and correlated subquery contexts by the cardinality each context permits.
Use EXISTS/NOT EXISTS for
existence questions and reason correctly about
IN/NOT IN when NULL is possible.
Explain correlation without assuming a literal nested-loop execution for every correlated query.
Use LATERAL for per-row top-N or dependent
derived relations.
Validate relational results before interpreting EXPLAIN/EXPLAIN ANALYZE output.
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. Subquery shape is a contract about rows and columns
| Context | Required shape | Typical use |
|---|---|---|
| Scalar subquery | one column, at most one row | one derived value in SELECT/WHERE |
| Row comparison subquery | one row with matching column count | compare a composite key/value |
| Table subquery in FROM | any declared relation shape | build a derived relation with its own scope |
| EXISTS subquery | content ignored; only zero vs one-or-more rows matters | membership/existence |
| Correlated subquery | references an outer query value | derive/filter in relation to each outer row |
| LATERAL FROM item | derived relation may reference preceding FROM items | per-row top-N, expansion, dependent functions |
A scalar subquery that returns zero rows produces NULL. If it returns more than one row, PostgreSQL raises an error because the scalar contract is violated.
SELECT c.customer_id, c.customer_name, (SELECT count(*) FROM app.ch05_work_order w WHERE w.customer_id = c.customer_id) AS work_order_countFROM app.ch05_customer cORDER BY c.customer_id;-- Deliberately wrong: customer 1 has several work orders.SELECT c.customer_id, (SELECT w.work_order_id FROM app.ch05_work_order w WHERE w.customer_id = c.customer_id) AS one_work_orderFROM app.ch05_customer cWHERE c.customer_id = 1;ERROR: more than one row returned by a subquery used as an expression
If you mean “latest work order,” make that rule explicit with
ordering and LIMIT 1; if you mean all work orders,
use a relation-valued shape instead of forcing it into a scalar
slot.
3. EXISTS asks a Boolean question and does not need output columns
EXISTS (subquery) is TRUE if the subquery can
produce at least one row. PostgreSQL is free to stop once
existence is established, so do not rely on side effects or full
evaluation inside the subquery.
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 = 'open')ORDER BY c.customer_id;customer_id | customer_name------------+----------------------1 | Northwind Health2 | Alpine Manufacturing4 | Delta Foods
The reference to c.customer_id makes the subquery
correlated. That describes name scope and relational dependence.
It does not require PostgreSQL to execute a naïve “run the inner
query once per outer row” algorithm; the planner can transform
equivalent forms.
4. IN and NOT IN inherit three-valued logic
IN is closely related to equality against any row.
NOT IN is the negation of that membership test. If
no equal value is found but the right side contains NULL, the
result can be UNKNOWN rather than TRUE. In a WHERE clause,
UNKNOWN is filtered out.
SELECT 11 IN (10, NULL) AS in_result, 11 NOT IN (10, NULL) AS not_in_result, 10 NOT IN (10, NULL) AS definitely_false;in_result | not_in_result | definitely_false----------+---------------+-----------------NULL | NULL | f
This becomes dangerous when a subquery column is nullable. A
robust anti-membership pattern is NOT EXISTS with
an explicit equality condition, because it asks whether a
matching row exists rather than asking whether one value is
unequal to every member of a possibly unknown set.
SELECT t.technician_id, t.technician_nameFROM app.ch05_technician tWHERE NOT EXISTS ( SELECT 1 FROM app.ch05_work_order w WHERE w.assigned_technician_id = t.technician_id AND w.status = 'open')ORDER BY t.technician_id;technician_id | technician_name--------------+----------------10 | Ada12 | Grace5. Correlated scalar queries need an explicit single-row rule
“Latest event type per work order” is a scalar business question if exactly one value should be displayed. Ordering by event time and a unique tie-breaker, then limiting to one row, makes that rule deterministic.
SELECT w.work_order_id, (SELECT e.event_type FROM app.ch05_work_order_event e WHERE e.work_order_id = w.work_order_id ORDER BY e.event_at DESC, e.event_id DESC LIMIT 1) AS latest_eventFROM app.ch05_work_order wWHERE w.work_order_id IN (1001,1002,1004,1009)ORDER BY w.work_order_id;work_order_id | latest_event--------------+-------------1001 | closed1002 | assigned1004 | NULL1009 | closed
This is correct, but when you need multiple columns from the
selected event, repeating correlated scalar subqueries becomes
noisy and can duplicate work. That is where
LATERAL gives a cleaner relational shape.
6. LATERAL means “this FROM item may depend on earlier FROM items”
A normal subquery in FROM cannot refer to sibling
FROM items that precede it unless it is marked
LATERAL. With
LEFT JOIN LATERAL ... ON true, each work order can
produce zero or one selected event while preserving work orders
that have no events.
SELECT w.work_order_id, latest.event_type, latest.event_at, latest.detailFROM app.ch05_work_order wLEFT JOIN LATERAL ( SELECT e.event_type, e.event_at, e.detail FROM app.ch05_work_order_event e WHERE e.work_order_id = w.work_order_id ORDER BY e.event_at DESC, e.event_id DESC LIMIT 1) AS latest ON trueWHERE w.work_order_id IN (1001,1002,1004,1009)ORDER BY w.work_order_id;work_order_id | event_type | event_at | detail--------------+------------+-------------------------+----------------1001 | closed | 2026-08-01 10:00:00+00 | restored1002 | assigned | 2026-08-02 09:10:00+00 | Linus1004 | NULL | NULL | NULL1009 | closed | 2026-08-06 12:00:00+00 | pump replaced
For set-returning functions in FROM, PostgreSQL
permits references to preceding FROM items even without writing
the LATERAL keyword, but spelling out the
dependency can still improve readability. For sub-SELECTs, the
keyword is required.
7. Per-row top-N is a natural LATERAL use case
SELECT w.work_order_id, e.event_type, e.event_atFROM app.ch05_work_order wCROSS JOIN LATERAL ( SELECT event_type, event_at, event_id FROM app.ch05_work_order_event e WHERE e.work_order_id = w.work_order_id ORDER BY event_at DESC, event_id DESC LIMIT 2) eWHERE w.work_order_id IN (1001,1009)ORDER BY w.work_order_id, e.event_at DESC, e.event_id DESC;
CROSS JOIN LATERAL drops outer rows that produce no
inner rows. Use LEFT JOIN LATERAL ... ON true when
preserving the outer row is part of the requirement.
8. Result first, plan second
PostgreSQL can rewrite many subquery forms into joins,
semi-joins, anti-joins, aggregates, or parameterized paths. Do
not choose EXISTS because you expect a specific
node name; choose it because it precisely expresses existence.
Once the result is verified, use EXPLAIN to diagnose
cost/cardinality behavior.
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)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 = 'open')ORDER BY c.customer_id;EXPLAIN ANALYZE executes the statement. This lesson uses a read-only SELECT, so execution is safe. Later chapters that inspect write plans must wrap writes in a transaction and ROLLBACK when appropriate.
9. Hands-on lab: choose the subquery shape from the question
-- A. One scalar: total cost for customer 3.SELECT (SELECT sum(cost) FROM app.ch05_work_order WHERE customer_id = 3) AS customer_3_cost;-- B. Customers with no closed work orders.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 AND w.status = 'closed')ORDER BY c.customer_id;-- C. Latest event per work order with all selected event columns.SELECT w.work_order_id, x.event_type, x.event_atFROM app.ch05_work_order wLEFT JOIN LATERAL ( SELECT e.event_type, e.event_at, e.event_id FROM app.ch05_work_order_event e WHERE e.work_order_id = w.work_order_id ORDER BY e.event_at DESC, e.event_id DESC LIMIT 1) x ON trueORDER BY w.work_order_id;Check your understanding
- What happens when a scalar subquery returns zero rows? More than one row?
- Why can NOT IN unexpectedly filter every candidate when the right side contains NULL?
- What does correlation describe, and what does it not guarantee about physical execution?
- Why is LEFT JOIN LATERAL useful for “latest child row” when some parents have no children?
- Why should you validate result rows before comparing EXPLAIN plans?
Review the answers
A scalar subquery yields NULL for zero rows and errors for
more than one. NOT IN can become UNKNOWN
because of NULL on the right. Correlation describes
outer-name dependence, not a mandatory nested-loop
algorithm. LEFT JOIN LATERAL preserves an outer row even
when the dependent subquery returns none. Plans are
meaningful only after the relational result is known to be
correct.
10. Production judgment and bridge
Subqueries are not a performance smell by themselves; they are relational building blocks. Use the shape whose cardinality contract matches the problem, isolate NULL semantics, and let the planner choose an equivalent physical strategy. The next lesson scales composition further with named common table expressions and recursive traversal, where termination and materialization choices become explicit design concerns.