Chapter 05 · SQL Querying: Joins, Subqueries, CTEs, Set Operations, and LATERAL

SELECT Semantics, NULL, Three-Valued Logic, Ordering, and Expression Evaluation

Build PostgreSQL SELECT queries from relational semantics, reason correctly about NULL and three-valued logic, guarantee deterministic ordering, resolve expression types, and avoid relying on unspecified expression-evaluation order.

Intermediate120–150 minutesQuery semantics + NULL/order labCurrent patched PostgreSQL 18.xCore SQL; no extensions requiredLast reviewed: August 2026

Learning outcomes

ServiceHub needs a dashboard for open and recently closed work orders. A first draft seems trivial—write SELECT, add a few predicates, and sort. Then three production-grade bugs appear: rows with NULL values disappear unexpectedly, “latest 5” changes between executions, and a supposedly guarded division still raises division by zero. These are not planner curiosities; they are consequences of SQL semantics.

Explain the logical stages of a SELECT without confusing them with a physical execution plan.

Reason with SQL three-valued logic and use IS NULL, IS [NOT] DISTINCT FROM, and Boolean truth tests deliberately.

Guarantee deterministic result order with a complete ORDER BY contract, including tie-breaking and NULL placement.

Use CASE, COALESCE, and explicit casts while understanding common-type resolution.

Avoid depending on left-to-right Boolean or function-argument evaluation when correctness or safety depends on order.

Prerequisite connection

Course 01 introduced SELECT/filter/order syntax. Here the focus is PostgreSQL semantics: which rows conceptually exist at each query stage, how NULL propagates through Boolean logic, and why a plan may evaluate equivalent expressions in an order different from the source text.

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.

Safety boundary

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.

sql · reset and seed the deterministic ServiceHub query lab

\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. Start with relational meaning, not a guessed execution sequence

PostgreSQL documents a logical processing model for SELECT: source rows are formed from FROM, filtered by WHERE, grouped when needed, filtered at group level by HAVING, projected through the select list, optionally deduplicated, combined by set operations, ordered, and finally limited. That sequence is a semantic model. It does not mean the executor must scan, join, sort, and compute expressions in that textual order.

Clause	Relational question	Do not infer
`FROM` / joins	What candidate row combinations exist?	That tables are physically scanned left-to-right.
`WHERE`	Which candidate rows satisfy TRUE?	That predicates are evaluated in written order.
`GROUP BY` / aggregates	Which rows form each group and what aggregate values exist?	That grouping must use a visible sort.
`SELECT`	Which expressions become output columns?	That every expression is evaluated before LIMIT in every query shape.
`DISTINCT`	Which duplicate output rows are removed?	That the surviving row has meaningful “first” identity.
`ORDER BY`	What ordering contract is requested?	That heap or index order is a substitute.
`LIMIT`	Which prefix/subset of the ordered result is returned?	That LIMIT without ORDER BY identifies stable rows.

sql · one query, several semantic stages

SELECT city,       count(*) AS work_orders,       sum(cost) AS total_costFROM app.ch05_work_orderWHERE status <> 'cancelled'GROUP BY cityHAVING count(*) >= 2ORDER BY total_cost DESC, cityLIMIT 3;

text · deterministic result for the seeded dataset

city     | work_orders | total_cost---------+-------------+-----------Baku     |           6 |    1185.00Sumqayit |           2 |     200.00

The result follows the relational definition. Whether PostgreSQL uses a sequential scan, an index, hash aggregation, or a sort is an optimization question. Change the data or indexes and the plan may change while the correct result remains the same.

3. NULL means unknown or absent—not an ordinary value

SQL Boolean expressions have three truth states: TRUE, FALSE, and UNKNOWN, represented by NULL. A WHERE clause keeps only rows for which its condition is TRUE. FALSE and UNKNOWN are both rejected. This is why closed_at = NULL is not a valid null test: equality with an unknown value is itself unknown.

sql · observe three-valued logic directly

SELECT work_order_id,       closed_at = NULL AS wrong_test,       closed_at IS NULL AS is_openish,       status = 'open' OR closed_at IS NULL AS open_or_unfinishedFROM app.ch05_work_orderWHERE work_order_id IN (1002, 1006)ORDER BY work_order_id;

text · expected truth values

work_order_id | wrong_test | is_openish | open_or_unfinished--------------+------------+------------+-------------------1002          | NULL       | t          | t1006          | NULL       | f          | f

PostgreSQL also provides IS DISTINCT FROM and IS NOT DISTINCT FROM. They compare values while treating NULL as a comparable marker, so their result is always TRUE or FALSE rather than UNKNOWN.

sql · null-safe equality and inequality

SELECT NULL = NULL AS ordinary_equality,       NULL IS NOT DISTINCT FROM NULL AS null_safe_equal,       10 IS DISTINCT FROM NULL AS null_safe_different;

text · expected result

ordinary_equality | null_safe_equal | null_safe_different------------------+-----------------+--------------------NULL              | t               | t

Constraint connection

A CHECK constraint is satisfied when its expression is TRUE or UNKNOWN; it rejects FALSE. A WHERE clause, by contrast, emits only TRUE rows. This distinction is why NOT NULL and CHECK constraints often need to be designed together.

4. Deterministic ordering requires a complete contract

Without ORDER BY, PostgreSQL may return rows in any order the chosen plan finds convenient. With an ORDER BY that has ties, the tied rows are still free to appear in implementation-dependent order. Pagination, “latest N”, and reproducible exports therefore need enough sort keys to establish the order you actually mean.

sql · weak versus deterministic latest-work ordering

-- Incomplete: many rows can share priority/opened_at values.SELECT work_order_id, priority, opened_atFROM app.ch05_work_orderORDER BY priority, opened_at DESCLIMIT 5;-- Better: work_order_id is the unique final tie-breaker.SELECT work_order_id, priority, opened_atFROM app.ch05_work_orderORDER BY priority ASC, opened_at DESC, work_order_id DESCLIMIT 5;

NULL placement is also part of the ordering contract. PostgreSQL defaults to NULLS LAST for ascending order and NULLS FIRST for descending order, but production queries are clearer when business intent is explicit.

sql · place unfinished work before completed work explicitly

SELECT work_order_id, closed_atFROM app.ch05_work_orderORDER BY closed_at ASC NULLS FIRST, work_order_id;

5. CASE and COALESCE produce values—and still need type agreement

CASE chooses among result expressions; COALESCE returns the first non-null argument. PostgreSQL resolves their branches/arguments to a common output type. This is useful, but implicit conversion should not become a substitute for a clear contract.

sql · classification and common-type inspection

SELECT work_order_id,       CASE         WHEN status = 'closed' THEN 'done'         WHEN status = 'cancelled' THEN 'void'         ELSE 'active'       END AS lifecycle,       COALESCE(actual_minutes, estimated_minutes) AS observed_or_estimated,       pg_typeof(COALESCE(actual_minutes, estimated_minutes)) AS duration_typeFROM app.ch05_work_orderWHERE work_order_id IN (1001,1002,1006)ORDER BY work_order_id;

text · expected result

work_order_id | lifecycle | observed_or_estimated | duration_type--------------+-----------+-----------------------+--------------1001          | done      |                   110 | integer1002          | active    |                    60 | integer1006          | void      |                    10 | integer

If branches cannot be reconciled, PostgreSQL reports a type error. In public APIs or migration code, explicit casts document intent and prevent surprising changes when a branch is edited later.

6. Do not depend on left-to-right expression evaluation

SQL is declarative. PostgreSQL may reorder Boolean expressions when that preserves semantics, and operator/function inputs are not promised to execute left-to-right. Therefore “I put the safety predicate first” is not a correctness mechanism.

sql · the tempting but unsafe guard

-- Do not rely on the written order of these predicates.SELECT work_order_id,       actual_minutes::numeric / estimated_minutes AS ratioFROM app.ch05_work_orderWHERE estimated_minutes <> 0  AND actual_minutes::numeric / estimated_minutes > 1.0;

For this particular arithmetic case, the best repair is to make division itself safe or algebraically avoid it. NULLIF turns a zero divisor into NULL, and comparisons against that NULL become UNKNOWN rather than raising an error.

sql · safe expression independent of predicate order

SELECT work_order_id,       round(actual_minutes::numeric / NULLIF(estimated_minutes, 0), 4) AS ratioFROM app.ch05_work_orderWHERE actual_minutes::numeric / NULLIF(estimated_minutes, 0) > 1.0ORDER BY work_order_id;

text · expected result

work_order_id | ratio--------------+--------------------1001          | 1.22221005          | 1.25001009          | 2.0000

When evaluation order truly is part of the business rule, a CASE expression can create a conditional boundary. Even then, do not use side-effecting functions merely to observe evaluation order; planners may pre-evaluate immutable constants and may transform equivalent expressions.

7. Deliberately wrong query: “latest open work” without a total order

A dashboard author writes WHERE status = 'open' LIMIT 2 and sees work orders 1002 and 1007. They conclude those are the “latest two.” The query never stated “latest”, so that conclusion is unsupported. A sequential scan, index scan, changed statistics, VACUUM, or different LIMIT can expose a different subset.

sql · repair the business requirement, not the current plan

SELECT work_order_id, opened_atFROM app.ch05_work_orderWHERE status = 'open'ORDER BY opened_at DESC, work_order_id DESCLIMIT 2;

text · expected deterministic result

work_order_id | opened_at--------------+------------------------1010          | 2026-08-07 10:00:00+001007          | 2026-08-05 11:00:00+00

8. Hands-on lab: prove semantics before looking at a plan

Run the bootstrap, then answer each question with result rows first. Only after the result is correct should you run EXPLAIN. This discipline prevents “the plan looks fast” from disguising a relational bug.

sql · semantic verification tasks

-- 1. Which rows are not completed, treating NULL closed_at explicitly?SELECT work_order_id, status, closed_atFROM app.ch05_work_orderWHERE closed_at IS NULLORDER BY work_order_id;-- 2. Make NULL equality observable.SELECT work_order_id,       actual_minutes IS NOT DISTINCT FROM estimated_minutes AS exact_matchFROM app.ch05_work_orderORDER BY work_order_id;-- 3. Explain only after checking the expected rows.EXPLAIN (COSTS ON)SELECT work_order_id, opened_atFROM app.ch05_work_orderWHERE status = 'open'ORDER BY opened_at DESC, work_order_id DESCLIMIT 2;

Check your understanding

Why does WHERE closed_at = NULL return no TRUE matches?
What does IS NOT DISTINCT FROM add compared with ordinary equality?
Why is ORDER BY priority insufficient for deterministic pagination when priorities repeat?
Why should a safe arithmetic expression not depend on the left predicate being evaluated first?
What is the difference between the logical SELECT processing model and an EXPLAIN plan?

Review the answers

Equality with NULL normally yields UNKNOWN, while WHERE keeps only TRUE. IS NOT DISTINCT FROM provides null-safe equality. Repeated sort keys leave ties unordered, so add a unique tie-breaker. PostgreSQL may reorder Boolean expressions, so make the risky expression itself safe (for example with NULLIF) or use a genuinely conditional expression. Logical processing defines the relational result; EXPLAIN describes one chosen physical strategy.

9. Production judgment and bridge

Correct querying starts with a result contract: explicit null policy, explicit ordering, explicit type intent, and no hidden dependency on evaluation order. These rules are portable SQL ideas with PostgreSQL-specific details around ordering defaults, type resolution, and planner behavior. The next lesson adds multiple relations, where the same discipline becomes cardinality reasoning: before asking whether a join is fast, predict how many rows it is supposed to produce.

Authoritative references

Keep knowledge open

Help the academy stay free and grow.

If these tutorials save you time, a small donation supports new lessons, technical review, diagrams, examples, and long-term maintenance.

ETHEthereum / ERC-20 only

0x716c4Ab160C4B66F31a28AE2448BfF68fc3a2ef0

Send only assets compatible with the Ethereum/ERC-20 network. Do not send TRC-20/TRON assets.