MariaDB / Basic: relational foundations / Lesson 4

Filtering and deterministic results

Translate a warehouse question into a precise SELECT. Control missing values, boolean precedence and ordering before using pagination.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Compose predictable predicates and projections.
Explain deterministic order and safe parameters.

State the intended rows and columns

A SELECT should answer a specific question: which pending shipments cost at least twenty units, and which fields does the caller need? Projection chooses columns; WHERE chooses rows. Avoid SELECT * in an application contract because schema changes silently change its shape and can expose unnecessary data. SQL does not promise insertion order or primary-key order without ORDER BY. A LIMIT without deterministic ordering can therefore return a different subset after a plan changes.

Filter a small synthetic relation

This derived-table fixture needs no persistent table and contains three deliberately different statuses and amounts. The outer query selects only pending rows whose fee meets the threshold. Parentheses make the conjunction explicit, and the identifier breaks equal-fee ties. The expected result has shipment 3 before shipment 1 because the fee is descending. The result is derived from the literal rows, rather than presented as an execution trace from a configured database.

sql Illustrative SQL; expected results are reasoned, not measured.

SELECT shipment_id, fee FROM (
 SELECT 1 AS shipment_id, 'pending' AS state, 20 AS fee
 UNION ALL SELECT 2, 'sent', 50
 UNION ALL SELECT 3, 'pending', 30
) AS s
WHERE state = 'pending' AND (fee >= 20)
ORDER BY fee DESC, shipment_id ASC;

Expected output

shipment_id | fee
3           | 30
1           | 20

Only rows satisfying both predicates survive. ORDER BY defines the display order.

Three-valued logic needs deliberate handling

AND binds more tightly than OR, so pending OR urgent AND paid is not the same as (pending OR urgent) AND paid. Write parentheses to communicate intent. Comparisons with NULL yield unknown, which WHERE does not retain. NOT IN is especially surprising if its list or subquery contains NULL: apparently missing values may all disappear. For anti-membership against nullable data, a carefully correlated NOT EXISTS often expresses the intended relationship more clearly.

Use boundaries that keep their meaning

Use a half-open interval for a day when timestamps may contain fractional seconds: at or after the start, and before the next day. This avoids assuming the last representable time is 23:59:59. Keyset pagination uses the last ordered key rather than skipping a growing OFFSET. The example assumes shipment_id increases and the caller requests the next page after 100; the actual returned identifiers depend on the training fixture and are not fabricated here.

sql Illustrative SQL; expected results are reasoned, not measured.

SELECT shipment_id, created_at FROM shipments
WHERE created_at >= '2026-10-01 00:00:00'
  AND created_at <  '2026-10-02 00:00:00'
  AND shipment_id > 100
ORDER BY shipment_id LIMIT 20;

This is a query pattern requiring a shipments fixture; it does not define a real server target.

Parameters carry values, not SQL structure

An application should bind the state and threshold through its connector instead of concatenating user text into SQL. Parameters represent values, not arbitrary column names or ORDER BY fragments; choose allowed sort expressions from application code. LIMIT controls result size but does not necessarily control work examined by the engine. Once correctness is established, inspect a plan and indexes. Never make a query faster by removing the predicate that expresses authorization or business scope.

Common errors and fixes

A page repeats or skips tied rows

Why it happens: Ordering lacks a unique tie-breaker.
How to fix it: Append a stable unique key and include all ordering components in a cursor.
Prevent it next time: Test pagination with duplicate sort values and concurrent inserts.

NOT IN returns no rows unexpectedly

Why it happens: A NULL in the comparison set made the predicate unknown.
How to fix it: Filter NULL deliberately or use a correlated NOT EXISTS with correct equality.
Prevent it next time: Include NULL cases when reviewing exclusion logic.

A search accepts injected SQL

Why it happens: Untrusted text was concatenated as statement syntax.
How to fix it: Bind values through the connector and allowlist structural choices.
Prevent it next time: Centralize query construction and review dynamic ORDER BY code.

Check your understanding

1. Three pending shipments have fees 30, 30 and 20 with IDs 4, 7 and 8. Which first two rows should ORDER BY fee DESC, shipment_id ASC return?

Show worked solution

IDs 4 and 7, in that order. The fee tie is resolved by the ascending identifier, so the result is stable for unchanged data. Ordering only by fee does not promise whether 4 or 7 comes first. For a cursor on this mixed ordering, compare fee and identifier together, not identifier alone.

2. Explain why a day filter ending with <= 23:59:59 can lose records, and repair it.