Formatting SQL so people can read it
The database does not care how a query is laid out, but the next person to debug it does. A few layout rules turn a 400-character single line into something you can scan in seconds and review line by line in a diff.
Before and after
Here is a realistic query as it often arrives, pasted from a log on one line:
select c.id,c.name,count(o.id) as orders,sum(o.total) as spent from customers c left join orders o on o.customer_id=c.id and o.status='paid' where c.created_at>='2030-01-01' group by c.id,c.name having sum(o.total)>100 order by spent desc limit 20;
The same query after applying the rules below:
SELECT
c.id,
c.name,
COUNT(o.id) AS orders,
SUM(o.total) AS spent
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
AND o.status = 'paid'
WHERE c.created_at >= '2030-01-01'
GROUP BY
c.id,
c.name
HAVING SUM(o.total) > 100
ORDER BY spent DESC
LIMIT 20;
Nothing about the logic changed, but now you can see at a glance that the paid-status filter sits in the join condition, not in WHERE. That detail matters in a LEFT JOIN: a filter in ON keeps customers who have no matching orders, while the same filter in WHERE removes them. In this query the HAVING line drops low spenders anyway, but delete it and the two placements give different results. Good formatting makes that kind of decision visible.
The core rules
- Start each major clause on a new line:
SELECT,FROM, eachJOIN,WHERE,GROUP BY,HAVING,ORDER BY,LIMIT. - Put one selected column per line when there are more than two or three. Adding or removing a column then changes exactly one line in a diff.
- Indent what belongs to a clause, such as the column list or the
ONconditions of a join. - Pick one case for keywords and keep it. Uppercase keywords with lowercase table and column names is the most common convention because it separates the language from your data model.
- Use explicit
ASfor aliases and choose short but meaningful ones.cfor customers is fine in a small query; in a long one, prefercust. - Put spaces around operators:
o.total > 100, noto.total>100.
Leading or trailing commas?
| Style | Looks like | Pro | Con |
|---|---|---|---|
| Trailing | c.id, then c.name | Reads like normal text | Removing the last column leaves a dangling comma on the line above |
| Leading | c.id then , c.name | Commenting out any line except the first stays valid | Looks unusual to newcomers |
Either is fine. What matters is that the whole codebase uses the same one, ideally enforced by a formatter so nobody argues about it in review.
Replace deep nesting with CTEs
A subquery inside a subquery inside a FROM forces the reader to start in the middle. A common table expression (a WITH clause) names each step and lets the query read from top to bottom:
WITH paid_orders AS (
SELECT customer_id, total
FROM orders
WHERE status = 'paid'
),
customer_totals AS (
SELECT customer_id, SUM(total) AS spent
FROM paid_orders
GROUP BY customer_id
)
SELECT c.name, t.spent
FROM customers AS c
JOIN customer_totals AS t ON t.customer_id = c.id
WHERE t.spent > 100;
Each CTE can be run and checked on its own, which makes debugging much faster.
Formatting by hand: a checklist
- Split the single line at every clause keyword.
- Break the column list into one item per line.
- Give each join its own line, with
ONconditions indented beneath it. - Write each
ANDorORinWHEREon its own line, and add parentheses whenever both appear, becauseANDbinds tighter thanOR. - End the statement with a semicolon.
Common mistakes
SELECT *in saved queries. It hides which columns the code depends on and breaks when a column is added. List columns explicitly.- Implicit joins.
FROM a, b WHERE a.id = b.a_idmixes join logic with filters. WriteJOIN … ON. - Reformatting and changing logic in one commit. Do the layout change on its own, so reviewers can trust that the diff changes nothing.
Questions
Why is there no formatter tool on this page?
SQL has many dialects, and a correct formatter needs a full parser for each one. A half-correct formatter can change string literals or comments. Use an established formatter or linter in your editor or build, such as SQLFluff or your database IDE’s built-in format command, and keep the rules above as the style guide.
Does formatting affect performance?
No. Whitespace and keyword case are ignored by the parser. Some systems cache plans by exact query text, so keep the formatting of a frequently run query stable.