SpunkArt
Data, text and encoding

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

  1. Start each major clause on a new line: SELECT, FROM, each JOIN, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.
  2. 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.
  3. Indent what belongs to a clause, such as the column list or the ON conditions of a join.
  4. 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.
  5. Use explicit AS for aliases and choose short but meaningful ones. c for customers is fine in a small query; in a long one, prefer cust.
  6. Put spaces around operators: o.total > 100, not o.total>100.

Leading or trailing commas?

StyleLooks likeProCon
Trailingc.id, then c.nameReads like normal textRemoving the last column leaves a dangling comma on the line above
Leadingc.id then , c.nameCommenting out any line except the first stays validLooks 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 ON conditions indented beneath it.
  • Write each AND or OR in WHERE on its own line, and add parentheses whenever both appear, because AND binds tighter than OR.
  • 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_id mixes join logic with filters. Write JOIN … 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.