A Production SQL Query Review Checklist That Catches Expensive Mistakes

A SQL query can be syntactically valid, return plausible rows, and still create a production problem. It may scan a table that has grown tenfold, lock more rows than intended, return duplicate entities after a join, or expose data that the caller should not see. A production review needs more than a formatter and a quick test in a local database. It needs a checklist that follows the data path from request to result and makes operational assumptions explicit.

Verify the question before optimizing the query

Ask what decision the query supports and what one row represents. A report of customers with recent orders is different from a report of orders with customer details. That distinction determines whether aggregation, deduplication, and joins are correct. Define the expected result with a small example: two customers, several orders, a cancelled order, and a customer with no orders. If the reviewer cannot predict the result for that sample, an execution plan will not solve the underlying ambiguity.

Then review filters. Confirm that tenant, account, or authorization boundaries appear in the query or are enforced by a trusted layer. Dates should state whether the ending boundary is inclusive and which time zone the stored values use. Null handling should be deliberate. A condition such as status != 'archived' does not include null statuses on many databases, which may be correct or may hide records unexpectedly.

Read joins and writes with extra care

Every join should have a reason and a cardinality expectation. A one-to-many join can multiply rows, so a sum, count, or pagination query may become wrong even though individual columns look familiar. For left joins, keep conditions that decide whether the joined record exists in the ON clause. For updates and deletes, start by running the matching SELECT with the exact predicate. Consider a transaction, a row limit where the database supports it, and a backup or rollback plan for valuable data.

Parameterized queries are the default. Do not build SQL by concatenating untrusted strings, even when the input is “only an ID” or “only a sort field.” Dynamic identifiers need an allowlist, not an escaping guess. Review database permissions too: an application account that can delete every row can turn a small application bug into a recovery event.

Check cost using realistic conditions

Use EXPLAIN or the database equivalent on a representative environment. Look for full table scans on large tables, inefficient nested loops, missing join indexes, and sorts that spill to disk. A query that runs in milliseconds on ten local rows may need a different index or an incremental approach on millions. Measure with realistic parameters, particularly for broad date ranges and customers with unusually large datasets.

• Confirm the result shape and authorization boundary.
• Test nulls, empty relationships, duplicates, and large inputs.
• Inspect query plans before accepting a performance claim.
• Separate a formatting change from a behavior change in review.

The purpose of a checklist is not to slow shipping. It is to make expensive failure modes visible while the change is still cheap to correct. Good SQL review combines readable formatting, a concrete data example, safe parameter handling, and evidence about real execution cost.