An SQL Review Checklist

Questions to ask of every query

Is the grain of each table clear, and do joins avoid fan-out? Are LEFT JOIN filters in ON? Are NULLs handled (NOT EXISTS instead of NOT IN, COALESCE where needed)? Is every ORDER BY deterministic with a tiebreaker? Are date ranges half-open? Are predicates sargable, and do indexes match the most important filters? Have data changes been previewed in a transaction? Has EXPLAIN ANALYZE been checked on realistic data volumes?

The checklist

Use it in code reviews.

[ ] grain of each table stated; no fan-out double counting
[ ] LEFT JOIN conditions on the right table placed in ON
[ ] NULL-safe logic: NOT EXISTS, IS DISTINCT FROM, COALESCE
[ ] ORDER BY with tiebreakers; LIMIT only with ORDER BY
[ ] half-open date ranges (>= start AND < next_start)
[ ] window functions filtered in an outer query
[ ] sargable predicates; indexes match WHERE/ORDER BY columns
[ ] UNION ALL unless deduplication is needed
[ ] DML previewed in BEGIN ... ROLLBACK, RETURNING checked
[ ] EXPLAIN ANALYZE on realistic data; estimates close to actuals

Test queries with tricky data

Include NULLs, ties, empty groups and duplicate keys in test data; most SQL bugs hide there.

Quick check: Which belongs on an SQL review checklist?

• Put LEFT JOIN filters in WHERE
• Always use SELECT *
• Use NOT EXISTS rather than NOT IN with nullable subqueries
• Rely on default row order