Filter rows with WHERE
A SELECT statement can read a large table, but applications usually need only a subset. The WHERE clause defines a condition for retaining rows. Conditions can compare columns with values, combine checks with AND and OR, test a range with BETWEEN, or match members of a list with IN. Use parentheses to make mixed boolean logic explicit. A condition should express the business question precisely: filtering active products by category is different from filtering products that are active or happen to belong to a category.
Compare values using compatible types. MySQL may coerce strings and numbers during comparison, and implicit conversion can produce surprising results or prevent an index from being used effectively. Use quoted literals for strings and dates, numeric literals for numbers, and parameter binding for application values. A text column's collation also influences equality and ordering, including whether case differences matter. See operators and expressions and NULL handling.
Order results intentionally
ORDER BY defines the order of returned rows. Without it, row order is unspecified even if the same query appears to return rows consistently during development. Specify ASC or DESC to show the intended direction. When the main sort value can repeat, add a unique tie-breaker such as a primary key. This is especially important when results are paginated: if tied rows have no defined relative order, records may appear on more than one page or be skipped as the data changes.
SELECT product_id, name, price
FROM products
WHERE active = TRUE
AND price BETWEEN 10 AND 80
ORDER BY price ASC, product_id ASC
LIMIT 20;This query returns at most twenty active products whose price is between the inclusive endpoints, sorted from least expensive to most expensive. The identifier establishes deterministic order among products with the same price. The result is not necessarily the twenty cheapest products overall if other filters are added; always reason about the combined predicate.
Limit results and understand pagination
LIMIT caps the number of rows returned. MySQL also accepts an offset, commonly written as LIMIT 20 OFFSET 40, to skip earlier rows. Offset pagination is simple and works well for shallow pages, but a large offset can require MySQL to find and discard many records. Changes between requests can shift the page boundaries. For large, frequently changing datasets, keyset pagination can continue after the last seen ordering key instead; compare strategies in MySQL pagination.
Filtering and ordering can benefit from indexes, but index selection depends on data distribution and query shape. An index that supports equality filtering and the requested sort may avoid extra work, while a low-selectivity predicate may still make a table scan reasonable. Do not add an index solely because a column appears in WHERE. Inspect the access plan with EXPLAIN and measure using representative row counts.
Common mistakes and safe patterns
A common mistake is writing a broad OR condition without parentheses and receiving more rows than intended. Another is using LIMIT without ORDER BY and assuming it returns the same records every time. For a date range, a half-open interval—greater than or equal to the start and less than the next boundary—handles fractional seconds more safely than guessing the final instant of a day. Avoid applying functions to indexed columns in filters unless you have checked whether a functional or generated-column index is appropriate for the deployed version.
Use a bounded result set for exploratory work and explicitly select columns required by the caller. Parameterize values in application code and validate any user-selected sort field against an allowlist; SQL parameters cannot generally substitute identifiers. These habits make retrieval predictable, safer, and easier to tune. Continue with SELECT fundamentals, index design, and joins.