184: SELECT, Projection, Filtering, Expressions, Ordering, LIMIT, and Pagination

Learning outcomes

By the end of this lesson, you can:

explain and apply projection in a realistic implementation;
explain and apply where predicates in a realistic implementation;
explain and apply expressions and aliases in a realistic implementation;
explain and apply order by in a realistic implementation;
explain and apply limit and offset in a realistic implementation.

Prerequisites and retrieval

This lesson assumes the earlier 01–06 foundation and the preceding lessons in this module. Before you read, retrieve one concrete example from a previous project in which you faced this same kind of decision. Perhaps you selected more columns than the client needed, accepted an unstable sort order, or implemented a page number without considering concurrent writes. The purpose is not to memorize vocabulary. It is to make a defensible choice in a PostgreSQL-backed transactional application, where schema design, correctness, query plans, and concurrency all affect the result.

Terminology

Projection: Select explicit columns in application queries instead of SELECT *. This keeps the result contract stable, bounds network and decoding work, and prevents a later schema addition from silently changing the payload.
WHERE predicates: Use comparisons, boolean logic, ranges, pattern matching, membership, and existence predicates deliberately to decide which rows qualify.
Expressions and aliases: Computed expressions, casts, date arithmetic, and aliases let SQL shape result values close to the data that produces them.
ORDER BY: Without ORDER BY, the database does not guarantee row order. Any apparent order you observe is an implementation detail, not a contract.
LIMIT and OFFSET: Offset pagination is straightforward and useful for small or administrative datasets. Large offsets still make the database process skipped rows, and concurrent writes can make page contents drift.
Keyset pagination: Cursor or keyset pagination continues from the last value in a stable ordering by using that ordered key in the next query's predicate.

Mental model

Treat SELECT, Projection, Filtering, Expressions, Ordering, LIMIT, and Pagination as a design problem with observable inputs, outputs, invariants, and failure modes. A query should return only the rows and columns the caller needs. When the result is paginated or its sequence is visible to a user, its order should also be deterministic. A strong implementation makes its assumptions visible, narrows uncertainty at system boundaries, and leaves evidence such as tests, types, constraints, metrics, or diagrams that explain why the design is safe.

A useful interview and production sequence is:

requirement -> constraints -> model -> implementation -> failure analysis -> verification

Do not leap from a requirement straight to a library call. First write down what must remain true. Then choose the SQL and application mechanisms that enforce those conditions. This is especially useful for pagination: “show the next 20 orders” is a requirement, while deterministic ordering, a maximum page size, and behavior during concurrent inserts are constraints that make the requirement precise.

Deep dive

1. Projection

When an application query uses SELECT *, its output changes whenever the table changes. That can increase transfer and decoding work, expose fields the caller did not need, and couple a response contract to the whole table shape. Select explicit columns instead so the repository's output is intentional and schema additions do not silently become API changes.

Decision rule: Use projection deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design. The columns should correspond to what the caller actually needs, not simply to what happens to be available in the table.

2. WHERE predicates

WHERE determines which rows qualify before the rest of the result is returned. Comparisons, boolean combinations, ranges, pattern matching, membership, and existence checks each express a different kind of condition, so write the predicate to match the requirement rather than assembling a convenient string. Parameterize values instead of interpolating them. That prevents SQL injection and lets the database and driver reuse the statement safely.

Decision rule: Use where predicates deliberately when they make the contract or invariant easier to prove. If a predicate only reduces typing while hiding an assumption, prefer the more explicit design. Be clear about boundary values, NULL behavior, and how multiple conditions combine; those details are often the source of “missing” rows.

3. Expressions and aliases

Computed expressions, casts, date arithmetic, and aliases can shape a result close to the data that supplies it. For example, an aggregate can be returned under a name the application understands, rather than forcing the application to infer what an unnamed expression means. Keep a business rule in SQL when database-side execution improves correctness or performance and the rule remains understandable there. Do not scatter a rule across SQL, repository mapping, and presentation code without a clear ownership boundary.

Decision rule: Use expressions and aliases deliberately when they make the contract or invariant easier to prove. If they only reduce typing while hiding an assumption, prefer the more explicit design. An alias improves the result's shape, but it does not change the underlying value or turn an expression into a stored column.

4. ORDER BY

Without ORDER BY, row order is not guaranteed. A query may appear to return rows in insertion order during testing and return them differently after an index, plan, or data-volume change. That distinction matters immediately for pagination and for any user-visible sequence.

Pagination needs a total deterministic key. If the main sort column is not unique, add a tie-breaker such as the primary key. A sort such as created_at DESC, id DESC gives rows with the same timestamp a defined relative order, which lets the next page describe exactly where it should continue.

Decision rule: Use order by deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design. State whether NULL values, ascending versus descending order, and ties are part of the expected behavior.

5. LIMIT and OFFSET

LIMIT bounds how many rows the caller receives. OFFSET skips rows before the returned slice, which makes page-number interfaces easy to implement. That simplicity is useful for small or administrative datasets, but it has a cost: a large offset still requires the database to work through the skipped portion, and concurrent inserts or deletes can shift rows between requests. A user may then see a duplicate or miss an item while moving between pages.

Decision rule: Use limit and offset deliberately when they make the contract or invariant easier to prove. If they only reduce typing while hiding an assumption, prefer the more explicit design. Bound the accepted page size rather than trusting the client to send a reasonable value, and inspect plans when the dataset or offset becomes large.

6. Keyset pagination

Cursor or keyset pagination continues from the last ordered key with a stable predicate. If the ordering is created_at DESC, id DESC, the next request can use the last row's (created_at, id) values to ask for rows after that position in the same ordering. This avoids walking through every preceding page and is generally a better fit for deep or continuously changing feeds.

The trade-off is interface complexity. Keyset pagination works well when “next” and “previous” are the important operations, but it is less convenient for an arbitrary “jump to page N” interface. The cursor also needs a defined format and should not be treated as trusted input merely because the server issued it.

Decision rule: Use keyset pagination deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design. Define the ordering, cursor contents, direction, and behavior when the referenced row has been deleted or new rows have appeared.

Worked example

Consider a PostgreSQL-backed transactional application in which schema design, correctness, query plans, and concurrency all matter. Start by stating the requirement in one sentence. Then list the input and output contracts and identify which concept owns each failure mode. The useful separation is this: parsing and validation belong at the boundary; domain rules belong in the domain or service layer; persistence rules belong in the database or repository; and presentation rules belong in the client. Mixing those concerns can make a happy-path demo look shorter, but it makes edge cases much harder to reason about.

SELECT c.id, c.name, COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE c.status = 'active'
GROUP BY c.id, c.name
ORDER BY order_count DESC;

There are several details worth noticing. Projection returns only the customer identifier, name, and computed count. The LEFT JOIN keeps an active customer in the result even when that customer has no orders, and COUNT(o.id) therefore reports zero for that customer rather than counting the null-extended join row. The WHERE predicate removes inactive customers before grouping. GROUP BY produces one aggregate row per selected customer, and the alias gives the computed count a readable name for ordering. This query is ordered by the count, but equal counts still have no guaranteed relative order; add a stable tie-breaker if this result will be paginated or displayed as a sequence.

Walk the example with at least four cases: the normal path, an empty or missing value, a duplicate, retry, or concurrent path where relevant, and a dependency failure. For each case, say which layer detects the problem and what the caller observes. For example, a malformed filter should normally be rejected at the request boundary, while a database connection failure belongs to the persistence or service error path. This is the level of explanation expected in a senior code review or technical interview.

Production perspective

Production correctness is broader than “the code works on my machine.” Ask how the design behaves during deploys, retries, partial failure, stale clients, concurrent requests, malformed data, schema changes, and high cardinality. Prefer explicit contracts, bounded resource usage, structured errors, and measurable behavior. Optimize only after evidence identifies a bottleneck or risk.

When the topic involves an external dependency, define a timeout and cancellation strategy. When it involves persistence, define transaction and consistency expectations. When it involves user-visible state, define loading, empty, error, stale, and success states. When it involves security, assume the client can be modified and all network input is untrusted. In particular, parameterize SQL values, enforce authorization on the server, and do not treat a client-provided page size, sort field, or cursor as automatically safe.

Guided lab

Build list endpoints for orders with filters, deterministic sorting, a bounded page size, and both offset and keyset versions. Compare query plans for page 1 and a deep page. The comparison should explain not just which query is faster, but what work each plan performs and how that work changes as the offset or result set grows.

Complete the lab with this discipline:

Write the requirement and two non-requirements.
List input, output, and error contracts before implementation.
Implement the smallest correct vertical slice.
Add at least one invalid-input test and one edge-case test.
Instrument or inspect the behavior instead of guessing.
Refactor one hidden assumption into an explicit type, constraint, function, or configuration.
Explain one alternative design and why you did not choose it.
Record a short “what would break at 10× scale?” note.

Edge cases and failure modes

Projection: Test absence, malformed input, duplicates, ordering or concurrency where applicable, and behavior at the smallest and largest credible sizes.
WHERE predicates: Test absence, malformed input, duplicates, ordering or concurrency where applicable, and behavior at the smallest and largest credible sizes. Include boundary values and NULL behavior when the predicate allows them.
Expressions and aliases: Test absence, malformed input, duplicates, ordering or concurrency where applicable, and behavior at the smallest and largest credible sizes. Verify types, rounding, null propagation, and the names returned to the caller.
ORDER BY: Test absence, malformed input, duplicates, ordering or concurrency where applicable, and behavior at the smallest and largest credible sizes. Include ties and confirm that the tie-breaker makes the order deterministic.
LIMIT and OFFSET: Test absence, malformed input, duplicates, ordering or concurrency where applicable, and behavior at the smallest and largest credible sizes. Include zero, the configured maximum, an oversized value, a deep offset, and changes made between page requests.

Common mistakes and debugging

Solving the example instead of the requirement: a copied pattern can be syntactically correct but architecturally wrong.
Hiding uncertainty with assertions, broad exception handlers, permissive schemas, or “temporary” any values.
Testing only the happy path and therefore discovering contracts only after integration.
Optimizing before measuring, or selecting a scalable mechanism without a scale requirement.
Letting client-side behavior stand in for server-side authorization, validation, or persistence guarantees.

For debugging, reproduce the smallest failing case and inspect the actual input, returned rows, and execution plan. Trace the boundary where the invariant first becomes false: source or build, browser or DOM, Network or HTTP, server or route, database or query, or deployment or configuration. An unexpected page can come from an omitted ORDER BY, a non-unique sort key, NULL predicate behavior, a changed row between requests, or an offset that is simply too expensive. Fix the owning layer rather than adding a downstream patch.

Interview questions

What problem does Projection solve, and what trade-off or failure mode would make you choose a different approach?
What problem does WHERE predicates solve, and what trade-off or failure mode would make you choose a different approach?
What problem does Expressions and aliases solve, and what trade-off or failure mode would make you choose a different approach?
What problem does ORDER BY solve, and what trade-off or failure mode would make you choose a different approach?
What problem does LIMIT and OFFSET solve, and what trade-off or failure mode would make you choose a different approach?

Checkpoint

Without notes, explain SELECT, Projection, Filtering, Expressions, Ordering, LIMIT, and Pagination to another developer in five minutes. Your explanation must include one invariant, one edge case, one production failure mode, and one alternative design. Then implement a small example without copying the lesson code. Be able to explain why its selected columns, predicates, ordering, and pagination strategy match the requirement.

Mastery checklist

I can define the core terms precisely.
I can choose a design from requirements instead of from habit.
I can implement and test the normal path and edge cases.
I can explain the runtime, storage, or complexity cost.
I can identify which layer owns validation, errors, and recovery.
I can compare at least two reasonable alternatives.
I can explain how the design changes at larger scale or stricter reliability.