Filtering with precise predicates

Audience and objective

You can read a small table and follow a programming example. No database server is needed. Combine filters without changing the requested boundaries.

Explanation

WHERE keeps rows whose predicate evaluates true. Comparisons operate on column values; quoted text is a literal, while an unquoted name generally refers to a column. AND requires both conditions. OR allows either. Parentheses make combinations explicit instead of relying on remembered precedence.

Write boundary examples before the query: exactly 500, exactly 1200, zero, and a value outside the range. BETWEEN includes both endpoints. A strict positive test excludes zero. Conditions are business rules, not opportunities to guess what the author probably meant.

Runnable worked example

This complete SQLite example creates synthetic tables in a fresh in-memory database. In the practice editor, the tables are already supplied: submit only the final SELECT/WITH query.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, city TEXT, email TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, amount_cents INTEGER, status TEXT, ordered_on TEXT);
CREATE TABLE returns (id INTEGER PRIMARY KEY, order_id INTEGER, amount_cents INTEGER);
INSERT INTO customers VALUES (1, 'Ada', 'Austin', 'a@example.test');
INSERT INTO customers VALUES (2, 'Bo', 'Dallas', NULL);
INSERT INTO customers VALUES (3, 'Cy', 'Austin', 'a@example.test');
INSERT INTO orders VALUES (1, 1, 1200, 'paid', '2026-01-02');
INSERT INTO orders VALUES (2, 1, 800, 'refunded', '2026-01-03');
INSERT INTO orders VALUES (3, 2, 1200, 'paid', '2026-02-01');
INSERT INTO orders VALUES (4, 99, 0, 'pending', '2026-02-02');
INSERT INTO returns VALUES (1, 1, 200);
INSERT INTO returns VALUES (2, 1, 100);
INSERT INTO returns VALUES (3, 2, 800);
SELECT id FROM orders WHERE status = 'paid' ORDER BY id;

Expected columns and rows:

{"columns": ["id"], "values": [[1], [3]]}

Filter before projecting the identifiers.

Common mistakes

Do not substitute > for >=. Do not filter on an output alias for portable SQL; use the source expression or a named intermediate query.

Guided practice

Trace the worked query one stage at a time. State the source grain, any filtered rows, and the output grain. Predict the empty-table result before running it. Then change one source value at a boundary and explain exactly which output rows should change.

Independent practice

Recap

Combine filters without changing the requested boundaries. Keep aliases, missing-value behavior, duplicate handling and ordering part of the public contract. Use SQLite syntax here; test dialect-specific details before moving the query to another database.