1.2 SQL Query Semantics, Joins, Aggregations, and Execution Plans

The records archivist delivers a report that runs but produces incorrect numbers, your task is to trace the error back to issues in joins, null values, or aggregation semantics.

SQL is a declarative language: a query specifies the desired relation, and the optimizer determines an efficient physical execution plan. But "declarative" does not mean "you can ignore semantics." JOIN multiplicity, NULL handling, and grouping rules can still cause a syntactically valid query to yield wrong results.

This lesson continues using vault-lab.db from Lesson 1.1.

Basic Projection, Filtering, and Sorting

sql

SELECT item_id, name, value_cents
FROM vault_items
WHERE value_cents >= 50000
ORDER BY value_cents DESC, item_id ASC
LIMIT 3;

The SELECT list determines the output expressions;
The WHERE clause filters input rows;
ORDER BY is the only general way to guarantee stable row ordering; without ORDER BY, row order is not guaranteed;
LIMIT should be used in conjunction with a deterministic ORDER BY, especially for pagination.

The surface syntax order of SQL does not match its logical execution order. A simplified model to understand this is:

text

FROM/JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT

The optimizer can rewrite and reorder the physical execution plan while preserving semantics, so this logical model should not be interpreted as requiring the engine to process rows one by one in this exact sequence.

predicate and three-valued logic

sql

SELECT item_id, name
FROM vault_items
WHERE discovered_on IS NULL;

Using ordinary comparisons like =, <>, and < with NULL typically results in UNKNOWN. A common pitfall is NOT IN: if the right-hand set contains NULL, the result might have no TRUE rows at all.

sql

-- Prefer NOT EXISTS when expressing anti-join semantics.
SELECT v.item_id, v.name
FROM vault_items AS v
WHERE NOT EXISTS (
    SELECT 1
    FROM quest_items AS qi
    WHERE qi.item_id = v.item_id
);

The example above requires the quest_items created in a later section. The key point is to make the correlation predicate explicitly express "no matching rows."

Establishing Many-to-Many Relationships

sql

CREATE TABLE adventurers (
    adventurer_id INTEGER PRIMARY KEY,
    name          TEXT NOT NULL UNIQUE,
    level         INTEGER NOT NULL CHECK (level >= 1)
);

CREATE TABLE quests (
    quest_id      INTEGER PRIMARY KEY,
    adventurer_id INTEGER NOT NULL,
    status        TEXT NOT NULL CHECK (
        status IN ('active', 'completed', 'cancelled')
    ),
    reward_cents  INTEGER NOT NULL CHECK (reward_cents >= 0),
    FOREIGN KEY (adventurer_id)
        REFERENCES adventurers(adventurer_id)
        ON DELETE RESTRICT
);

CREATE TABLE quest_items (
    quest_id INTEGER NOT NULL,
    item_id  INTEGER NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    PRIMARY KEY (quest_id, item_id),
    FOREIGN KEY (quest_id) REFERENCES quests(quest_id) ON DELETE CASCADE,
    FOREIGN KEY (item_id) REFERENCES vault_items(item_id) ON DELETE RESTRICT
);

INSERT INTO adventurers (adventurer_id, name, level)
VALUES (1, 'Eileen', 15), (2, 'Marco', 22), (3, 'Su He', 9);

INSERT INTO quests (quest_id, adventurer_id, status, reward_cents)
VALUES
    (1, 1, 'completed', 50000),
    (2, 2, 'active',    30000),
    (3, 1, 'active',    15000);

INSERT INTO quest_items (quest_id, item_id, quantity)
VALUES (1, 3, 1), (1, 5, 2), (2, 1, 1), (3, 5, 3);

quests and vault_items are many-to-many, so they are represented using an associative table quest_items, with (quest_id, item_id) designated as a composite primary key.

INNER JOIN and Row Multiplication

sql

SELECT
    q.quest_id,
    a.name AS adventurer_name,
    q.status,
    v.name AS item_name,
    qi.quantity
FROM quests AS q
JOIN adventurers AS a
  ON a.adventurer_id = q.adventurer_id
JOIN quest_items AS qi
  ON qi.quest_id = q.quest_id
JOIN vault_items AS v
  ON v.item_id = qi.item_id
ORDER BY q.quest_id, v.item_id;

A quest with two items generates two rows. This isn't "data duplication in the database," but rather the natural result of row multiplication in a join. If you later sum the quest rewards but forget that each item row contributes a duplicate reward, you'll end up with an incorrect total.

First, verify the cardinality of each join key: one-to-one, one-to-many, or many-to-many. This determines the appropriate level at which to perform aggregation.

LEFT JOIN and Placement of Conditions

Find all adventurers, including Su He who has no quest:

sql

SELECT
    a.adventurer_id,
    a.name,
    q.quest_id,
    q.status
FROM adventurers AS a
LEFT JOIN quests AS q
  ON q.adventurer_id = a.adventurer_id
ORDER BY a.adventurer_id, q.quest_id;

If you only want to join on active quests while still retaining adventurers without active quests, the predicate should be placed in the ON clause:

sql

SELECT a.name, q.quest_id
FROM adventurers AS a
LEFT JOIN quests AS q
  ON q.adventurer_id = a.adventurer_id
 AND q.status = 'active';

If q.status = 'active' is moved into the WHERE clause, rows with NULL values will be filtered out, resulting in a query that behaves nearly like an INNER JOIN. This is one of the most common semantic mistakes when using outer joins.

GROUP BY and Aggregation

sql

SELECT
    t.code,
    COUNT(*) AS item_count,
    MIN(v.value_cents) AS min_value_cents,
    MAX(v.value_cents) AS max_value_cents,
    ROUND(AVG(v.value_cents), 2) AS avg_value_cents
FROM vault_items AS v
JOIN item_types AS t
  ON t.item_type_id = v.item_type_id
GROUP BY t.item_type_id, t.code
HAVING AVG(v.value_cents) >= 50000
ORDER BY avg_value_cents DESC;

COUNT(*) counts the rows within each group;
COUNT(column) only counts rows where the expression is not NULL;
Most aggregation functions ignore NULL values;
Aggregation functions on empty inputs return 0 for COUNT(*), while functions like SUM/AVG typically return NULL;
Columns selected in the SELECT clause that are not part of an aggregation must semantically match the grouping key.

SQLite exhibits lenient behavior regarding non-standard grouping, so performance or execution speed should not be taken as evidence of portability or result determinism.

Subquery and CTE

Find items with a value higher than the overall average value in the database:

sql

SELECT item_id, name, value_cents
FROM vault_items
WHERE value_cents > (
    SELECT AVG(value_cents)
    FROM vault_items
)
ORDER BY value_cents DESC;

A CTE can assign a name to an intermediate relation:

sql

WITH quest_totals AS (
    SELECT
        q.adventurer_id,
        SUM(q.reward_cents) AS total_reward_cents
    FROM quests AS q
    WHERE q.status = 'completed'
    GROUP BY q.adventurer_id
)
SELECT
    a.name,
    COALESCE(qt.total_reward_cents, 0) AS total_reward_cents
FROM adventurers AS a
LEFT JOIN quest_totals AS qt
  ON qt.adventurer_id = a.adventurer_id
ORDER BY total_reward_cents DESC, a.adventurer_id;

CTEs are semantic organization tools and do not guarantee materialization or performance improvement. Actual optimization behavior depends on the database engine and version.

Indexes Serve Querying, Not Automatic Acceleration

sql

CREATE INDEX idx_vault_items_type_value
ON vault_items (item_type_id, value_cents DESC);

EXPLAIN QUERY PLAN
SELECT item_id, name, value_cents
FROM vault_items
WHERE item_type_id = 1
ORDER BY value_cents DESC;

The column order in a composite index must be carefully designed in conjunction with predicate conditions, sorting requirements, and selectivity. Even after an index is created, the query optimizer may still choose a table scan, especially if the table is small, the predicate covers a large portion of rows, statistics are incomplete, or a full scan is actually cheaper.

The cost of an index includes write amplification, storage overhead, cache consumption, and maintenance effort. Begin by defining key queries and service level objectives (SLOs), then validate these with real data distributions and execution plans.

EXPLAIN QUERY PLAN is a high-level execution plan summary from SQLite and is not a cross-database standard format. In PostgreSQL, EXPLAIN (ANALYZE, BUFFERS) represents an actual query execution; before using ANALYZE for write operations or expensive queries, it's essential to clearly understand the side effects and costs involved.

Pagination Boundaries

sql

SELECT item_id, name, value_cents
FROM vault_items
ORDER BY value_cents DESC, item_id DESC
LIMIT 20 OFFSET 10000;

A large OFFSET can still require scanning or skipping a large number of records, and concurrent writes can cause cross-page results to drift. Stable timelines or APIs typically use keyset pagination:

sql

SELECT item_id, name, value_cents
FROM vault_items
WHERE
    value_cents < :last_value
    OR (value_cents = :last_value AND item_id < :last_id)
ORDER BY value_cents DESC, item_id DESC
LIMIT 20;

The parameter names are illustrative; the actual placeholder syntax is determined by the driver. Sorting must include a unique tie-breaker.

Exercise

Count the number of active quests for each adventurer, including those with zero active quests.
Identify items that are not referenced by any quest, using both NOT EXISTS and a LEFT anti-join to achieve the same result.
Intentionally move the active predicate from ON to WHERE, and compare whether Su He still appears in the result.
Create a composite index on quests(adventurer_id, status), record the query plan changes, then delete most of the test data and observe whether the optimizer continues to make the same execution choice.
Within a transaction, change the type of an item to a non-existent ID, and verify that the foreign key constraint is enforced.

Acceptance Criteria

[ ] Explicitly use ORDER BY when a stable query order is required;
[ ] Explain the result cardinality of INNER/LEFT JOIN operations;
[ ] Distinguish the impact of WHERE and ON clauses on outer joins;
[ ] Explain COUNT(*) and COUNT(column);
[ ] Not assume that the query optimizer’s choice of index guarantees index effectiveness after creation;
[ ] Bind inputs through parameters;
[ ] Use clear transaction boundaries and changed-row verification for write operations.

Chapter Summary

The difficulty with SQL isn't memorizing keywords; it's expressing relationships correctly. Constraints enforce validity by rejecting illegal states, transactions define the boundaries of data changes, and queries produce results through predicates, joins, and aggregations. The next chapter will establish a more precise formal model for these queries using relational algebra.

Chapter 1: Propositional Logic: From Requirement Sentences to Decidable Formulas

Chapter 2: Sets and Operations: Describing Object Scope via Membership

Chapter 3: Direct Proof, Contrapositive, and Case Analysis: Building Verifiable Reasoning Chains

Chapter 4: Recursive Definitions and Mathematical Induction: Proving Along the Construction Process

Chapter 5: Binary Relations: Properties, Composition, and Closure

Chapter 6: Counting Principles: Addition, Multiplication, Inclusion-Exclusion, and the Pigeonhole Principle

Chapter 7: Graph Models and Traversal: First Define Edges, Then Choose Representation

Chapter 8: Probability Space and Conditional Probability: First Build the Model, Then Update Your Judgment

Chapter 9: Divisibility, Congruence, and Euclid's Algorithm: The Fundamental Structure of Integers

Chapter 10: Vectors and Inner Products: Turning Objects into Comparable Coordinates

Chapter 11: Limits, Derivatives, and the Chain Rule: Describing Local Change

Chapter 12: Self-Information, Entropy, and Coding: How Uncertainty Becomes Bits