MySQL / Relational foundations and precise SQL / Lesson 2

Selecting, filtering, and missing values

Turn a business request into a deterministic query and reason about SQL three-valued logic.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Project named columns and build precise predicates.
Handle NULL, date boundaries, sorting, and pagination.

Define the requested rows

Use the unchanged shop fixture from Relational foundations. A report request should specify its entity, selected attributes, filtering rule, and ordering. SELECT chooses expressions; FROM names their source; WHERE retains rows whose predicate is true. False and unknown predicates both remove a row. Returning named columns also prevents future schema additions from silently changing an application response.

Combine conditions deliberately

AND binds more tightly than OR, so write parentheses whenever business alternatives meet shared conditions. Numeric equality should compare compatible types. LIKE uses percent for any sequence and underscore for one character; it is not an equality test. String matching depends on collation, so an apparently case-insensitive search does not prove that every deployment compares text identically.

sql Paid orders with a stable order

SELECT id, total FROM orders
WHERE status = 'paid' AND total >= 10.00
ORDER BY total DESC, id ASC;

Expected output

id | total
101 | 25.00
103 | 10.00

Orders 101 and 103 qualify, giving totals 25.00 and 10.00. The secondary id ordering resolves ties. Changing the comparison to greater than 10 excludes order 103; equality at the boundary is therefore an explicit business choice.

Unknown differs from empty

NULL denotes missing information. NULL = NULL is unknown, not true. Use IS NULL to find missing cities; use COALESCE only when the chosen fallback matches the report meaning. MySQL also provides the NULL-safe equality operator <=>. Avoid NOT IN when its candidate set can contain NULL: an unmatched value can yield unknown and unexpectedly disappear. NOT EXISTS is often clearer for absence queries.

sql Display absence without changing storage

SELECT id, COALESCE(city, 'Unknown') AS display_city
FROM customers WHERE city IS NULL ORDER BY id;
SELECT NULL = NULL AS ordinary, NULL <=> NULL AS safe;

Expected output

id | display_city
3 | Unknown
ordinary | safe
NULL | 1

Only Noor matches, and the display label is Unknown while stored city remains NULL. The second query returns SQL NULL and 1. Display substitution should never be mistaken for collecting a real city or for making ordinary equality NULL-safe.

Use half-open time intervals

For January orders, compare placed_at >= January 1 and placed_at < February 1. An upper bound ending at midnight on January 31 would miss nearly that entire day. DATETIME stores the supplied calendar value without TIMESTAMP-style session time-zone conversion. Agree on the application time convention and bind proper date values rather than relying on locale-specific text or implicit numeric conversion.

Ordering is a contract

LIMIT bounds returned rows, not necessarily work performed. Without ORDER BY there is no first page with defined meaning; an index never guarantees the query result order. ORDER BY placed_at, id gives a unique sequence. Large OFFSET values may require scanning discarded rows and can shift under concurrent inserts. Later performance work uses a last-seen key to request the next page consistently.

Common errors and fixes

An empty missing-city report.

Why it happens: WHERE city = NULL evaluates to unknown even for Noor.
How to fix it: Use city IS NULL, which explicitly tests absence.
Prevent it next time: Include missing and nonmissing values when reviewing every nullable filter.

ERROR 1054 (42S22): Unknown column.

Why it happens: A SELECT alias is referenced in WHERE before that alias is available there.
How to fix it: Repeat the source expression or filter an outer query.
Prevent it next time: Distinguish output names from source columns when reading query scope.

Unstable LIMIT results.

Why it happens: Rows were limited without a unique ORDER BY, leaving ties arbitrary.
How to fix it: Add the unique id as a final ordering column.
Prevent it next time: Document sort direction and tie handling in the report contract.

Check your understanding

1. Write a January range and predict how many fixture orders match.

Show worked solution

Use WHERE placed_at >= '2026-01-01' AND placed_at < '2026-02-01'. All three orders match. This interval includes every time on January 31 and excludes exactly the start of February. ORDER BY placed_at, id makes their presentation deterministic.

2. Predict WHERE city <> 'Cairo' and then include missing cities explicitly.