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

A SQL query that runs proves only that parsing and execution did not stop; it does not prove the report is correct. Join cardinality, NULL handling, and aggregation semantics can quietly skew the result.

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 uses catalog-lab.db from Lesson 1.1 with SQLite 3.37.0 or later. After reopening the CLI, run PRAGMA foreign_keys = ON outside a transaction; that setting is not stored in the database file. Execute SQL blocks in dependency order. Pagination snippets with named parameters require driver binding.

Basic Projection, Filtering, and Sorting

SELECT item_id, name, value_cents
FROM catalog_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:

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.

Predicates and Three-Valued Logic

SELECT item_id, name
FROM catalog_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.

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

The example above requires the task_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

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name          TEXT NOT NULL UNIQUE,
    level         INTEGER NOT NULL CHECK (level >= 1)
) STRICT;

CREATE TABLE tasks (
    task_id      INTEGER PRIMARY KEY,
    customer_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 (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE RESTRICT
) STRICT;

CREATE TABLE task_items (
    task_id INTEGER NOT NULL,
    item_id  INTEGER NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    PRIMARY KEY (task_id, item_id),
    FOREIGN KEY (task_id) REFERENCES tasks(task_id) ON DELETE CASCADE,
    FOREIGN KEY (item_id) REFERENCES catalog_items(item_id) ON DELETE RESTRICT
) STRICT;

INSERT INTO customers (customer_id, name, level)
VALUES (1, 'Customer A', 15), (2, 'Customer B', 22), (3, 'Customer C', 9);

INSERT INTO tasks (task_id, customer_id, status, reward_cents)
VALUES
    (1, 1, 'completed', 50000),
    (2, 2, 'active',    30000),
    (3, 1, 'active',    15000);

INSERT INTO task_items (task_id, item_id, quantity)
VALUES (1, 3, 1), (1, 5, 2), (2, 1, 1), (3, 5, 3);

tasks and catalog_items are many-to-many, so they are represented using an associative table task_items, with (task_id, item_id) designated as a composite primary key.

INNER JOIN and Row Multiplication

SELECT
    q.task_id,
    a.name AS customer_name,
    q.status,
    v.name AS item_name,
    qi.quantity
FROM tasks AS q
JOIN customers AS a
  ON a.customer_id = q.customer_id
JOIN task_items AS qi
  ON qi.task_id = q.task_id
JOIN catalog_items AS v
  ON v.item_id = qi.item_id
ORDER BY q.task_id, v.item_id;

The initial fixture should return 4 rows: task 1 has two items, while tasks 2 and 3 have one each. A task 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 task 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

List all customers, including Customer C, who has no task:

SELECT
    a.customer_id,
    a.name,
    q.task_id,
    q.status
FROM customers AS a
LEFT JOIN tasks AS q
  ON q.customer_id = a.customer_id
ORDER BY a.customer_id, q.task_id;

To attach only active tasks while keeping customers who have none, put the status condition in ON:

SELECT a.name, q.task_id
FROM customers AS a
LEFT JOIN tasks AS q
  ON q.customer_id = a.customer_id
 AND q.status = 'active';

If q.status = 'active' is moved into the WHERE clause, rows with NULL values will be filtered out, making the result equivalent to an INNER JOIN with the same condition. This is one of the most common semantic mistakes when using outer joins.

GROUP BY and Aggregation

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 catalog_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 permits some non-standard grouping. If an unaggregated column is not determined by the grouping key, successful execution does not establish a portable or deterministic value. The SQLite SELECT documentation explains these bare-column rules.

Subquery and CTE

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

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

A CTE can assign a name to an intermediate relation:

WITH task_totals AS (
    SELECT
        q.customer_id,
        SUM(q.reward_cents) AS total_reward_cents
    FROM tasks AS q
    WHERE q.status = 'completed'
    GROUP BY q.customer_id
)
SELECT
    a.name,
    COALESCE(qt.total_reward_cents, 0) AS total_reward_cents
FROM customers AS a
LEFT JOIN task_totals AS qt
  ON qt.customer_id = a.customer_id
ORDER BY total_reward_cents DESC, a.customer_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

CREATE INDEX idx_catalog_items_type_value
ON catalog_items (item_type_id, value_cents DESC);

EXPLAIN QUERY PLAN
SELECT item_id, name, value_cents
FROM catalog_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; check side effects and load before applying ANALYZE to writes or expensive queries.

Pagination Boundaries

SELECT item_id, name, value_cents
FROM catalog_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:

SELECT item_id, name, value_cents
FROM catalog_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. Keyset pagination reduces the work of skipping preceding rows, but does not freeze the data: a change to value_cents between requests can move a record across the cursor and cause omission or duplication. An export that needs a fixed result set also needs a snapshot or immutable ordering key.

Exercise

1. Query each customer's active-task count while retaining customers with zero tasks.
2. Identify items that are not referenced by any task, using both NOT EXISTS and a LEFT anti-join.
3. Intentionally move the active predicate from ON to WHERE, and compare whether the zero-task test customer still appears.
4. Create a composite index on tasks(customer_id, status), record the query plan changes, then delete most test data and observe whether the optimizer makes the same choice.
5. 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.