Search Knowledge

© 2026 LIBREUNI PROJECT

Database Systems/Data contracts, relations, and SQL
Page 4 of 20

SQL querying: specify rows, then test edge cases

Write PostgreSQL queries whose joins, filters, groups, NULL behavior, and ordering match an explicit result contract.

Lesson briefData contracts, relations, and SQL · 7 h · 3 outcomes

Required knowledge

  • Relational-model grain, keys, foreign keys, duplicate, and NULL concepts
  • Relational-algebra selection, projection, join, and difference

Learning outcomes

  • Trace the logical effect of FROM, ON, WHERE, GROUP BY, HAVING, SELECT, and ORDER BY on a small query.
  • Place a left-join predicate in ON or WHERE according to whether unmatched left rows must survive.
  • Write a NULL-safe anti-join and a grouped report with a stated output grain.

Assessment target

Given a report requirement and seed rows, write a PostgreSQL query, predict its result for NULL and no-match cases, and explain each clause's role.

Scope boundary

  • Window functions, recursive queries, optimizer tuning, and transaction isolation are introduced elsewhere.

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.

StageQuestion answeredTypical clause
Build candidate rowsWhich source rows and matches are available?FROM ... JOIN ... ON ...
Remove individual rowsWhich candidate rows satisfy a condition?WHERE
Form groupsWhich remaining rows belong together?GROUP BY
Remove groupsWhich computed groups qualify?HAVING
Shape outputWhich expressions become columns?SELECT
Present a sequenceWhat order is promised to the client?ORDER BY

Build a zero-safe allocation report

Use this service allocation fixture.

ServicePool:

pool_idservice_coderegion
10S101us-east-1
11S201us-east-1
12S301us-east-1
13S101us-west-2

Allocation:

client_idpool_idstatus
Ada10active
Bo11suspended

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.

SQL

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. ON decides 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. WHERE then removes the other-region pool 13. COUNT(a.client_id) counts only non-null matching IDs, yielding S101 → 1, S201 → 0, and S301 → 0.

Predict the bug

Move AND a.status = 'active' from ON to WHERE. Which service pool rows disappear, and why?

HintExpand

A left join creates NULLs for rows with no qualifying right-side match; WHERE runs afterward.

Worked solutionExpand

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

0 / 4 answered

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.

1
Placing 'a.memory_gb >= 70' in the ON clause preserves service pools with no matching allocations while suppressing allocations below 70 GB.
2
Placing 'p.region = us-east-1' in the WHERE clause discards service pools from other regions after joining.
3
Placing 'a.memory_gb >= 70' in the WHERE clause preserves current service pools that have zero qualifying allocations.
4
Replacing 'COUNT(a.client_id)' with 'COUNT(*)' correctly reports zero allocations for service pools lacking qualifying memory sizes.

Code Exercise

3 tokens to supply

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.

Interactive exploration

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.

The predicate limits matching Allocation rows, so every Host remains in the output.
Place a.memory_gb ≥ threshold
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;
Left-join result with the memory allocation predicate in ON
hostservicememory_gb
worker-01auth-svc87
worker-02NULLNULL
worker-03NULLNULL

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.

Place a left-join predicate; inspect which hosts survive

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

Left-join result with the memory allocation predicate in ON
hostservicememory_gb
worker-01auth-svc87
worker-02NULLNULL
worker-03NULLNULL

Predicate in WHERE: filter after NULL extension

Left-join result with the memory allocation predicate in WHERE
hostservicememory_gb
worker-01auth-svc87

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 in ON” also fails when the condition should remove left rows, such as p.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?

HintExpand

Ask whether each condition describes a left row that may survive and a right row that may match.

Worked solutionExpand

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. Moving COUNT(a.client_id) >= 1 to WHERE is invalid because WHERE is 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.

HintExpand

Reuse the three group counts from the earlier report.

Worked solutionExpand

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 EXISTS is true. The query does not depend on a subquery list having no NULL values.

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?

HintExpand

Evaluate c.client_id <> NULL using SQL three-valued logic.

Worked solutionExpand

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.

Section Exercises

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

Descriptionrecall

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.

HintExpand

A LEFT JOIN produces NULL values for right attributes when no match occurs. WHERE filters after that generation.

Worked solutionExpand

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

Verificationapplication

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.

HintExpand

Evaluate the logical expansion of NOT IN with a NULL in the candidate set.

Worked solutionExpand

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

Constructionapplication

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.

HintExpand

Use WHERE to filter service pools by region, ON for allocation status, and HAVING for aggregate thresholds.

Worked solutionExpand
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

Generalisationtransfer

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.

HintExpand

Preserve unmatched service pools with LEFT JOIN and count non-null client IDs.

Worked solutionExpand
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.

References & Further Reading