SQL Formatting Guide

Why format SQL at all?

The database does not care about white space, but people do. A query is read many more times than it is written: in code review, during an incident, and months later by someone who has never seen it. A consistent layout makes the structure visible, makes mistakes easier to see, and makes version control show only the lines that really changed.

The rules most teams agree on

1. One clause per line. Start SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY and LIMIT on their own lines.
2. One column per line when there are several. It makes a diff show one changed column as one changed line.
3. Indent what belongs to a clause by one level: the columns, the join conditions and the extra AND / OR conditions.
4. Be consistent with keyword case. Uppercase keywords are the most common style, because they stand out from names. Lowercase is also fine if the whole team uses it.
5. Use AS for aliases, and give tables short but meaningful aliases (o for orders, not t3).
6. Write joins explicitly with JOIN ... ON, and not as a comma list with the condition hidden in WHERE.
7. Prefer CTEs (WITH name AS (...)) to deeply nested subqueries, and name each step by what it contains.
8. List the columns you need and avoid SELECT * in code that other people depend on.
9. Comment the why, not the what: a comment that says why a filter exists is useful, one that repeats the code is not.

Example: the same query in two styles

Trailing commas, uppercase keywords and a two-space indent are the default style of the formatter:

SELECT
  u.id,
  u.name,
  COUNT(o.id) AS orders,
  SUM(o.total) AS revenue
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
  AND o.status = 'paid'
WHERE u.active = 1
  AND (u.country IN ('US', 'CA') OR u.vip = 1)
GROUP BY
  u.id,
  u.name
HAVING COUNT(o.id) > 0
ORDER BY revenue DESC
LIMIT 10;

Leading commas, lowercase keywords and a four-space indent are popular in some teams, because a missing comma is easy to see and one column can be commented out without touching its neighbors:

select
    u.id
    , u.name
    , count(o.id) as orders
    , sum(o.total) as revenue
from users u
left join orders o
    on o.user_id = u.id
    and o.status = 'paid'
where u.active = 1
    and (u.country in ('US', 'CA') or u.vip = 1)
group by
    u.id
    , u.name
having count(o.id) > 0
order by revenue desc
limit 10;

Commas: trailing or leading?

Both styles are in wide use. Trailing commas read like normal text and like most code. Leading commas make a missing comma stand out, and allow the first column to be commented out without breaking the query. Choose one for the project and let a formatter enforce it.

Aligned (river) style

Some style guides right-align the keywords so that they form a vertical "river" with the content on the right. It looks tidy, but it needs manual work or a special tool, and it makes every line change when a keyword changes. Most formatters, including this one, use the simpler left-aligned, indented layout.

When to minify

Keep the formatted version in your source files. Minify only a copy that you must put on one line, such as a log line, a URL parameter or a string in a build. A minified query does not run faster. See the SQL minifier.

Common mistakes

• Different styles in one file. The worst case is a mix of two styles, so choose one and apply it to the whole project.
• Reformatting by hand. It takes time and it is not repeatable. Use a tool, and ideally run it in a pre-commit hook or in CI.
• Formatting inside strings. Never change text inside quotes. A good formatter leaves it alone.
• Mixing a style change with a logic change in one commit. Format first in its own commit, so that reviewers can see the real change.

Tools that format SQL

• This site: SQL formatter, SQL beautifier and SQL pretty print.
• On the command line: sqlformat from the Python package sqlparse, and pg_format (pgFormatter) for PostgreSQL.
• Code editors and database clients usually have a built-in or add-on SQL formatter. Search the settings for "format SQL".
• Linters, such as SQLFluff, check style rules in a project and can fix many of them automatically.

More on this site

• The same ideas for other languages: HTML formatter, CSS formatter, JavaScript formatter.
• YAML formatter and JSON diff for config and data files.