SQLite / Basic: the embedded engine and correct data / Lesson 4

SELECT, NULL, order, and limits

Turn a business question into a predictable SQLite result using explicit projections, predicates, missing-value logic, and complete ordering.

About 5 min readContent reviewed 2026-10-25

On this page

Lesson goals

Predict three-valued predicate behavior.
Use LIMIT with deterministic ORDER BY.
Choose clear text and date-range conditions.

Specify one output row and its fields

A query should state its output grain: one row per open order, one row per customer, or another defined unit. SELECT chooses expressions, FROM names sources, and WHERE selects individual source combinations. Request needed columns rather than relying on SELECT *, which couples callers to unrelated schema additions and can fetch large values unnecessarily. Aliases should explain calculations.

Logical processing explains why a SELECT alias is not a reliable portable input to an earlier filtering stage. SQLite permits some alias conveniences that other engines reject; prefer a clear derived table or repeat a justified expression when portability matters. Logical meaning is distinct from physical execution order, which the optimizer chooses without changing the promised result.

Unknown is not false or zero

NULL denotes missing information. Ordinary comparisons with it generally yield NULL, the unknown truth state, and WHERE retains true predicates only. Use IS NULL or IS NOT NULL to ask about missingness. amount <> 0 excludes unknown amounts as well as zeros, because unknown is not true. Add an explicit missing branch when the business question includes it.

The first script displays the accepted known amounts and then the missing identifier. It deliberately preserves zero as a distinct value. COALESCE is useful for a justified display fallback, but using it inside a predicate or aggregate changes the question. NOT IN is especially sensitive when its comparison set includes NULL; absence checks often read more clearly as correlated NOT EXISTS.

sql Distinguish unknown from measured zero

CREATE TABLE reading(id INTEGER PRIMARY KEY, amount REAL);
INSERT INTO reading VALUES(1,NULL),(2,0.0),(3,10.0),(4,20.0);
SELECT id FROM reading WHERE amount<>0 ORDER BY id;
SELECT id FROM reading WHERE amount IS NULL ORDER BY id;

Expected output

id
3
4

id
1

Ids3 and4 satisfy the inequality. Id1 is found only by the explicit null test, while id2 is a known zero.

Select a meaningful first page

LIMIT restricts row count and does not define which rows are first without ORDER BY. An observed insertion order, a rowid, or an index is not a presentation contract. Order by the business ranking and add a unique tiebreaker for a stable fixed-size selection. The second script chooses the two highest scores and orders equal scores by identifier.

The outermost query controls displayed order. Ordering inside a view or subquery does not automatically promise the caller's final order. OFFSET can implement simple pagination, but large offsets can require substantial skipped work and changes between requests can shift rows. Later lessons compare keyset continuation and snapshot requirements rather than treating pagination as a formatting-only choice.

sql A fixed-size ordered selection

CREATE TABLE score(id INTEGER PRIMARY KEY, points INTEGER NOT NULL);
INSERT INTO score VALUES(1,40),(2,90),(3,90),(4,70);
SELECT id,points FROM score ORDER BY points DESC,id ASC LIMIT 2;

Expected output

id|points
2|90
3|90

The two90-point rows are selected, with id2 before id3. The tie rule is visible instead of inferred from storage.

Make ranges and text interpretation explicit

AND binds more tightly than OR, so parentheses should expose grouped alternatives. For a consistently formatted timestamp column, a half-open interval includes the start and excludes the next boundary, avoiding guesses about a final fractional second. TEXT date ordering works only under a consistent sortable representation and time convention; mixed formats or zones invalidate that shortcut.

LIKE has its own wildcard and case behavior and is not universal Unicode matching. Bind values and escape wildcard characters when the product means a literal pattern. A parameter value is not a substitute for a column identifier or sort-direction keyword. Select dynamic structure from a fixed allowlist and keep values separately bound at the application boundary.

Test truth tables and boundary rows

Predict each row's inclusion before running the query. Include missing, zero, blank, tied, boundary, and out-of-range values in tiny examples. Compare key sets and ordering rather than only the count. A query returning two rows can still return the wrong two. Distinguish a syntactic failure from a valid statement expressing the wrong predicate.

Document whether a report is a live view or a consistent extraction and which data changes can occur between requests. Use the actual runtime's documented expression rules when porting SQL, including affinity and collation. A concise result contract connects source grain, selected fields, missingness, filters, and final ordering so another reader can explain the output without knowing the accidental table layout.

Common errors and fixes

A NULL comparison returned no rows.

Why it happens: The predicate used = NULL instead of a missingness operator.
How to fix it: Use IS NULL or IS NOT NULL as required.
Prevent it next time: Include unknown values in predicate tests.

A limited result changes unpredictably.

Why it happens: Ordering is absent or leaves ties unresolved.
How to fix it: Define final ORDER BY with a suitable tiebreaker.
Prevent it next time: Test tied ranking values.

A date range misses late-day records.

Why it happens: An inclusive end date was assumed to represent every timestamp that day.
How to fix it: Use an explicit half-open interval with consistent representation.
Prevent it next time: Test the exact start, end, and adjacent values.

Check your understanding

1. Which values survive amount<>10 when amounts are10,NULL,and30?

Show worked solution

Only30. The NULL comparison is unknown and is not retained by WHERE. If absence should be included, add OR amount IS NULL with parentheses appropriate to any other condition.

2. Return the latest three events when event times can tie. What must the query state?