How to explain a SQL query without guessing the plan
Read SQL by result, source, filter, group, order and row-bound stages, then use the target database plan for scans, joins, estimates and timing.
Explain what the statement asks for before explaining how it might run: name the result expressions, sources and joins, row filters, groups, post-group filters, ordering and row bounds. Then inspect the target database's plan for access paths, join strategy, estimates and runtime evidence. Never infer an index scan or cost from SQL text alone.
State the promised result before reading implementation detail
Begin with the SELECT list. Describe each output expression in plain language, including aggregates and aliases, without claiming what values will appear. An unqualified star is a request for visible columns, not a stable contract about their future order or count.
- Count the output expressions at the outer query level.
- Name calculations, aggregates and aliases separately.
- Mark DISTINCT as duplicate elimination, not proof that duplicates are erroneous.
- Record any output whose type depends on the real schema or function.
Trace each source and join condition
List every table, view, subquery or function in FROM. For each join, identify the left and right source, join kind and matching condition. Do not assume the written order is the execution order; the optimizer may choose a different plan while preserving query semantics.
Separate row filtering from group filtering
WHERE decides which rows remain eligible before grouping. GROUP BY forms groups for aggregate results. HAVING filters those groups after aggregation. Keeping the stages separate prevents a common explanation error: describing HAVING as though it removed source rows before COUNT, SUM or AVG was calculated.
Read ordering and row bounds last
ORDER BY defines the requested result order; without it, returned order is not promised. LIMIT, OFFSET or FETCH constrains the visible slice, but OFFSET still depends on the preceding order for repeatable pagination. These clauses describe semantics, not whether an index or temporary sort will be used.
Use EXPLAIN for the plan and ANALYZE with care
A database plan can reveal scan and join nodes, estimated rows, sort or aggregate steps and cost estimates using the real schema and statistics. ANALYZE variants can add actual timing and row counts by executing the statement in some engines, so use safe inputs, permissions and transactions before collecting runtime evidence.
Check the primary references
Check the source against the result
SELECT customers.region, COUNT(*) AS order_count FROM customers JOIN orders ON orders.customer_id = customers.id WHERE orders.created_at >= $1 GROUP BY customers.region HAVING COUNT(*) >= $2 ORDER BY order_count DESC LIMIT 10;
Return at most ten regions ordered by descending order count. Match orders to customers by customer ID, keep orders on or after the bound date, group the remaining rows by region, and keep only groups whose count reaches the second bound value.
The explanation covers result semantics and placeholder roles. It does not claim which table is scanned first, whether an index is used, how many rows qualify or what the query costs; those facts require the target database plan.
Start with a local clause map
Separate one supported SELECT into its visible stages and structural flags before moving to the target database plan.
Open SQL Explainer