SQL is concise enough to hide a wrong question. Before typing, say the output grain: “one row per service pool in us-east-1, including pools with zero active allocations.” That single sentence determines whether a filter belongs in ON, WHERE, or HAVING.
Follow the logical pipeline
For a common grouped query, reason in this order.
| Stage | Question answered | Typical clause |
|---|---|---|
| Build candidate rows | Which source rows and matches are available? | FROM ... JOIN ... ON ... |
| Remove individual rows | Which candidate rows satisfy a condition? | WHERE |
| Form groups | Which remaining rows belong together? | GROUP BY |
| Remove groups | Which computed groups qualify? | HAVING |
| Shape output | Which expressions become columns? | SELECT |
| Present a sequence | What order is promised to the client? | ORDER BY |
Build a zero-safe allocation report
Use this service allocation fixture.
ServicePool:
pool_id | service_code | region |
|---|---|---|
| 10 | S101 | us-east-1 |
| 11 | S201 | us-east-1 |
| 12 | S301 | us-east-1 |
| 13 | S101 | us-west-2 |
Allocation:
client_id | pool_id | status |
|---|---|---|
| Ada | 10 | active |
| Bo | 11 | suspended |
The report must include service pools 11 and 12 with count zero. Its grain is one service pool in us-east-1, not one joined allocation row.
Execute and predict
The runner seeds exactly this fixture in SQLite. State the three result rows before running the starting query, then move AND a.status = 'active' into WHERE and run it again. The difference is the lesson.
Zero-safe allocation report
Predict which service pools survive, including zero counts, before executing the query.
Read-only SQLite fixture; reloaded fresh on each execution (100-row display limit).
Ready. A fresh fixture is created for each query.
The analogous PostgreSQL statement is:
SELECT p.service_code,
COUNT(a.client_id) AS active_count
FROM ServicePool AS p
LEFT JOIN Allocation AS a
ON a.pool_id = p.pool_id
AND a.status = 'active'
WHERE p.region = 'us-east-1'
GROUP BY p.pool_id, p.service_code
ORDER BY p.service_code;
Worked example.
ONdecides which allocation rows match each service pool. The status condition keeps only active matches, while the left join still emits null-extended rows for pools with no such match.WHEREthen removes the other-region pool 13.COUNT(a.client_id)counts only non-null matching IDs, yieldingS101 → 1,S201 → 0, andS301 → 0.
Predict the bug
Move AND a.status = 'active' from ON to WHERE. Which service pool rows disappear, and why?
HintExpandCollapse
A left join creates NULLs for rows with no qualifying right-side match; WHERE runs afterward.
Worked solutionExpandCollapse
Service pools 11 and 12 disappear. Pool 11’s only matching allocation is suspended; pool 12 is null-extended because it has no allocation. Neither makes a.status = 'active' true in WHERE. The condition turns this part of the left join into inner-join-like behavior for that predicate.
Outer join predicate placement and null survival
A report must retain every target-region service pool from ServicePool while counting only matching Allocation rows where memory_gb >= 70. Evaluate each assertion regarding predicate placement in a LEFT JOIN query:
Classify each option as correct or incorrect, or leave uncertain options unanswered (“I don't know”) and verify at any time.
Code Exercise
Reconstruct the zero-safe report
SELECT p.service_code, AS active_count FROM ServicePool AS p LEFT JOIN Allocation AS a a.pool_id = p.pool_id AND a.status = 'active' WHERE p.region = 'us-east-1' p.pool_id, p.service_code ORDER BY p.service_code
See predicate placement change the result
The visual model fixes worker-01 at 87 GB, worker-02 at 40 GB, and worker-03 with no allocations. Set the threshold to 50 GB. Predict whether worker-03 survives before trying both placements.
Place a left-join predicate; inspect which hosts survive
worker-01 has 87 GB allocated, worker-02 has 40 GB, and worker-03 has no allocations. Move the memory condition between ON and WHERE, then compare the relation the query actually returns.
SELECT h.name, a.service, a.memory_gb
FROM Host h
LEFT JOIN Allocation a
ON a.host_id = h.host_id AND a.memory_gb >= 50;| host | service | memory_gb |
|---|---|---|
| worker-01 | auth-svc | 87 |
| worker-02 | NULL | NULL |
| worker-03 | NULL | NULL |
Model scope: one allocation per host in this sample, no duplicate rows, standard SQL NULL semantics, and no ordering guarantee. The example isolates predicate placement; real queries may introduce additional joins, aggregates, policies, or correlated predicates that need their own semantic check.
With a threshold of 50 GB, the two placements answer different questions. Compare the result tables before deciding where the predicate belongs.
Predicate in ON: preserve every host
| host | service | memory_gb |
|---|---|---|
| worker-01 | auth-svc | 87 |
| worker-02 | NULL | NULL |
| worker-03 | NULL | NULL |
Predicate in WHERE: filter after NULL extension
| host | service | memory_gb |
|---|---|---|
| worker-01 | auth-svc | 87 |
Static state: memory_gb ≥ 50 in ON preserves worker-01, worker-02, and worker-03; the same predicate in WHERE leaves only worker-01 because the NULL-extended and nonqualifying rows are filtered afterward.
Counterexample. “Put every filter in
WHERE” fails for the zero-safe report. “Put every filter inON” also fails when the condition should remove left rows, such asp.region = 'us-east-1'. Clause placement follows row survival, not a style rule.
Apply the row-survival test
The infrastructure team wants all us-east-1 service pools, but only allocations of 70 GB or more should be shown when an allocation exists. Where do p.region = 'us-east-1' and a.memory_gb >= 70 belong?
HintExpandCollapse
Ask whether each condition describes a left row that may survive and a right row that may match.
Worked solutionExpandCollapse
p.region = 'us-east-1' belongs in WHERE because an other-region service pool should disappear. a.memory_gb >= 70 belongs in ON if a us-east-1 pool must still appear when it has no qualifying allocation. This preserves the left relation’s required rows while changing which right rows qualify as matches.
Groups answer a different question from rows
WHERE filters rows before aggregation; HAVING filters a group after an aggregate has been computed.
SELECT p.service_code,
COUNT(a.client_id) AS active_count
FROM ServicePool AS p
LEFT JOIN Allocation AS a
ON a.pool_id = p.pool_id
AND a.status = 'active'
WHERE p.region = 'us-east-1'
GROUP BY p.pool_id, p.service_code
HAVING COUNT(a.client_id) >= 1;
Worked example. This version has the same group grain as the zero-safe report but removes groups with zero active allocations at
HAVING. MovingCOUNT(a.client_id) >= 1toWHEREis invalid becauseWHEREis evaluated before the count exists.
Trace a result
For the fixture above, what rows survive the HAVING version? Then state what one output row means.
HintExpandCollapse
Reuse the three group counts from the earlier report.
Worked solutionExpandCollapse
Only S101, 1 survives. Each output row means “one service pool in us-east-1 whose active allocation count is at least one.” It does not mean one client, because the grouping collapsed allocation rows.
Anti-joins: prefer a NULL-safe question
To ask for clients with no active allocation in region us-east-1, write the absence condition directly.
SELECT c.client_id
FROM Client AS c
WHERE NOT EXISTS (
SELECT 1
FROM Allocation AS a
JOIN ServicePool AS p ON p.pool_id = a.pool_id
WHERE a.client_id = c.client_id
AND a.status = 'active'
AND p.region = 'us-east-1'
);
Worked example. The correlated condition asks whether an active allocation row exists for this client. If none exists,
NOT EXISTSis true. The query does not depend on a subquery list having noNULLvalues.
Prediction with a NULL
Suppose a data-quality bug produces an allocation row with client_id = NULL. Why can WHERE c.client_id NOT IN (SELECT client_id FROM Allocation) return no clients, while the NOT EXISTS query above can still answer the intended question?
HintExpandCollapse
Evaluate c.client_id <> NULL using SQL three-valued logic.
Worked solutionExpandCollapse
The NOT IN condition is equivalent to a chain of inequalities. One comparison to NULL becomes UNKNOWN, so the whole predicate may fail to become true for every client. NOT EXISTS tests only whether a correlated row with equal non-null ID and active status exists; an unrelated null ID does not make that condition true.
Deep Practice
Solve these problems to test and consolidate your understanding. Complete worked solutions and diagnostic hints are available for each problem.
Exercise 1.4.1: ON versus WHERE Predicate Placement Semantics
Explain how moving an outer join filter predicate from the ON clause to the WHERE clause changes query semantics for null-extended rows. Give a concrete example where a service pool with zero allocations is erroneously discarded.
HintExpandCollapse
A LEFT JOIN produces NULL values for right attributes when no match occurs. WHERE filters after that generation.
Worked solutionExpandCollapse
In a LEFT JOIN, conditions in the ON clause decide whether right-side rows match, preserving all left rows. If the filter condition is moved to WHERE, it is evaluated on the joined result where right attributes are NULL. Comparisons against NULL evaluate to UNKNOWN in SQL’s three-valued logic, which WHERE treats as false, eliminating unmatched left-side rows and inadvertently converting the outer join into an inner join.
Exercise 1.4.2: Anti-Join NULL Semantics: NOT EXISTS vs. NOT IN
Suppose an Allocation table contains a single row with client_id = NULL. Verify why WHERE c.client_id NOT IN (SELECT client_id FROM Allocation) returns zero clients, while a correlated NOT EXISTS query correctly identifies clients without allocations.
HintExpandCollapse
Evaluate the logical expansion of NOT IN with a NULL in the candidate set.
Worked solutionExpandCollapse
c.client_id NOT IN (a1, a2, ..., NULL) expands to (c.id <> a1) AND (c.id <> a2) AND ... AND (c.id <> NULL). In SQL, c.id <> NULL yields UNKNOWN. Because TRUE AND UNKNOWN is UNKNOWN, the entire predicate evaluates to UNKNOWN for every client, discarding all rows. In contrast, NOT EXISTS checks whether the subquery returns any row; a row with NULL matching condition a.client_id = c.client_id evaluates to UNKNOWN and is not returned, leaving NOT EXISTS true.
Exercise 1.4.3: Grouped Aggregation with Post-Filter Specification
Write a PostgreSQL query against ServicePool(pool_id, service_code, region) and Allocation(client_id, pool_id, status) that returns the service code and active allocation count for all us-east-1 service pools having strictly more than 10 active allocations.
HintExpandCollapse
Use WHERE to filter service pools by region, ON for allocation status, and HAVING for aggregate thresholds.
Worked solutionExpandCollapse
SELECT p.service_code,
COUNT(a.client_id) AS active_count
FROM ServicePool AS p
JOIN Allocation AS a
ON a.pool_id = p.pool_id
AND a.status = 'active'
WHERE p.region = 'us-east-1'
GROUP BY p.pool_id, p.service_code
HAVING COUNT(a.client_id) > 10
ORDER BY p.service_code;Because the threshold requires > 10 allocations, an inner join is sufficient. GROUP BY includes pool_id to guarantee uniqueness per service pool.
Exercise 1.4.4: Zero-Safe Service Pool Report with Computed Boolean Status
Construct a query returning (service_code, active_count, is_full) for all service pools in region us-east-1 with capacity >= 1. Pools with zero allocations must appear with count 0 and is_full = false. Explain how the grain is preserved.
HintExpandCollapse
Preserve unmatched service pools with LEFT JOIN and count non-null client IDs.
Worked solutionExpandCollapse
SELECT p.service_code,
COUNT(a.client_id) AS active_count,
COUNT(a.client_id) >= p.capacity AS is_full
FROM ServicePool AS p
LEFT JOIN Allocation AS a
ON a.pool_id = p.pool_id
AND a.status = 'active'
WHERE p.region = 'us-east-1'
AND p.capacity >= 1
GROUP BY p.pool_id, p.service_code, p.capacity
ORDER BY p.service_code;The grain is one positive-capacity service pool in us-east-1. Placing a.status = 'active' in ON allows pools without active allocations to survive as null-extended rows, which COUNT(a.client_id) aggregates to 0.