SQL Formatting: Writing Readable, Maintainable Queries
Learn SQL formatting conventions, explicit joins, CTEs and parameterised queries to write readable, maintainable SQL that is easy to review.
.jpg)
Every codebase eventually collects a query that nobody wants to touch. It runs to eighty lines, the joins are tangled together, and the only person who understood it left last year. The logic may be fine. The layout is the problem. This guide shows how to format SQL so that queries are readable, reviewable and maintainable: the conventions that matter, the habits that cause trouble, and how to automate the tedious parts.
What is SQL, and why does formatting matter?
SQL, Structured Query Language, is the standard language for working with relational databases. It is declarative: you describe the result you want, and the database engine decides how to fetch it. The language is standardised by ISO and implemented, with differences, by PostgreSQL, MySQL, SQL Server, Oracle, SQLite and others. The PostgreSQL documentation is one of the clearest references for the syntax.
SQL ignores whitespace and, for keywords, letter case. These two statements are the same to the database:
select u.id,u.name,count(o.id) as orders from users u left join orders o on o.user_id=u.id where u.active=true group by u.id,u.name having count(o.id)>3 order by orders desc;
SELECT
u.id,
u.name,
COUNT(o.id) AS orders
FROM users AS u
LEFT JOIN orders AS o
ON o.user_id = u.id
WHERE u.active = TRUE
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 3
ORDER BY orders DESC;
Only the second is something you can review. Formatting matters for the same reasons it does in other languages, and a few that are specific to SQL:
- Queries are read far more often than written. They live in migrations, reports, dashboards and application code for years.
- Structure shows intent. When each clause starts a new line, you can see at once which tables are joined, how, and what is filtered.
- Diffs stay meaningful. One column per line means adding a column changes one line, not the whole statement.
- Bugs hide in dense queries. A misplaced
ORin a longWHEREclause, or a join condition in the wrong place, is easy to miss when everything is on one line. - Performance work needs clarity. Tuning a slow query starts with understanding it.
Core formatting conventions
There is no single official SQL style, but widely used guides agree on most points. The SQL Style Guide by Simon Holywell is a popular reference, and it is a good starting point for a team standard.
Capitalise keywords. Write SELECT, FROM, WHERE in uppercase and leave table and column names in lowercase. This separates the language from your schema at a glance. (Some teams prefer all lowercase. Either is fine if consistent.)
Start each major clause on a new line. SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY and LIMIT each begin a line.
One column per line in the select list. It makes diffs and comments easy, and it is simple to reorder.
Indent consistently. Put the items under a clause one level deeper, with joins and their ON conditions indented under the table.
Always use AS for aliases. Explicit AS is clearer than a bare alias, and it prevents accidents when a comma is missing.
Use meaningful aliases. u, o and oi are fine when short and obvious. Avoid a, b, c.
Put operators and conditions where they are easy to scan. Align AND and OR at the start of lines in a long WHERE clause:
WHERE o.status = 'paid'
AND o.created_at >= DATE '2026-01-01'
AND (o.channel = 'web' OR o.channel = 'app')
Parenthesise mixed AND/OR. SQL evaluates AND before OR, which surprises people. Brackets state the intent.
Name things consistently. Use snake_case, singular or plural table names (pick one), and avoid reserved words as identifiers.
End with a semicolon. It marks statement boundaries and prevents errors when scripts are concatenated.
Writing readable queries beyond layout
Formatting is the visible part. These habits make queries easier to understand and safer.
Avoid SELECT *
SELECT * is convenient in exploration but fragile in code. It fetches columns you do not need, hides what the query depends on, and breaks when the table changes. List the columns you want.
Use explicit joins
Write JOIN ... ON rather than listing tables with commas and filtering in WHERE. Explicit joins separate the relationship from the filter, and they prevent accidental cross joins.
-- Hard to review: relationship hidden in WHERE
SELECT o.id, c.name
FROM orders o, customers c
WHERE o.customer_id = c.id AND o.status = 'paid';
-- Clearer: relationship in ON, filter in WHERE
SELECT o.id, c.name
FROM orders AS o
INNER JOIN customers AS c
ON c.id = o.customer_id
WHERE o.status = 'paid';
Break complex queries into CTEs
Common Table Expressions, written with WITH, name intermediate steps so a long query reads from top to bottom like a short program:
WITH recent_orders AS (
SELECT customer_id, SUM(total) AS spend
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY customer_id
),
big_spenders AS (
SELECT customer_id
FROM recent_orders
WHERE spend > 1000
)
SELECT c.id, c.name
FROM customers AS c
INNER JOIN big_spenders AS b
ON b.customer_id = c.id;
Each block does one thing, and you can test it separately. CTEs are widely supported, including in PostgreSQL, SQL Server, MySQL 8 and SQLite.
Comment the why
A short comment explaining a business rule, a workaround, or a surprising filter is worth more than a comment restating the code.
-- Exclude test accounts created by the QA import (see ticket ENG-412)
WHERE u.email NOT LIKE '%@example.test'
Be careful with NULL
NULL is not equal to anything, including itself. WHERE col = NULL never matches. Use IS NULL and IS NOT NULL, and remember that NOT IN with a subquery returning NULL can return no rows.
Real-world use cases
Making sense of ORM or tool-generated SQL. Query logs, ORMs and BI tools produce one-line statements. Formatting is step one when debugging a slow or wrong query.
Code review. Reviewers can focus on logic when layout is consistent, and they can see exactly which lines changed.
Migrations and stored procedures. These files live for years. A consistent layout reduces mistakes during urgent changes.
Sharing queries. Whether you paste a query into a ticket, a chat or documentation, readable SQL gets faster, better answers.
Learning and teaching. Structured layout shows the logical order of a query: select, from, where, group, having, order.
Common mistakes
- One-line queries. Impossible to review and hard to diff.
- Implicit joins with filters in
WHERE. Hides relationships and invites accidental cross joins. - Mixed
ANDandORwithout brackets. Precedence bugs return wrong rows without any error. - Meaningless aliases.
t1,t2,xforce readers to scroll back and forth. - String-built queries with user input. Beyond style, this is a security hole. Use parameterised queries to avoid SQL injection. The OWASP SQL injection guidance explains the risk.
- Depending on column order.
INSERT INTO t VALUES (...)with no column list breaks when the schema changes. Name the columns. - Formatting data, not code. Reformatting string literals can change data. A good formatter leaves quoted strings alone.
Step-by-step: format SQL with DevUtilX
For a quick clean-up with no installation, use the DevUtilX SQL Formatter. It runs in your browser.
- Open the SQL formatter tool.
- Paste your query into the input editor.
- Choose your preferred indentation and keyword case.
- Run the formatter and read the structured output.
- Copy the result into your editor, ticket or documentation.
Because the formatting happens locally, the query text is not uploaded anywhere. That matters when a query contains table names, internal identifiers or sample data you would rather not share.
A formatter changes layout only. It does not validate your query against a database, so always run the result before relying on it.
Automating SQL formatting
On a team, formatting should happen automatically.
Use a SQL formatter or linter. Open-source tools such as sqlfluff can both lint and fix SQL for many dialects, and sql-formatter and similar libraries format queries in code. Pick one that supports your dialect, since PostgreSQL, MySQL and SQL Server differ in syntax.
Configure once, commit the config. Store the rules in the repository so everyone formats the same way.
Run in CI. Fail the build when a changed .sql file is not formatted, as you would for application code. Our guide to JavaScript code formatting describes the same pattern.
Keep it out of embedded strings, or format them deliberately. SQL inside application code is harder to format automatically. Keeping larger queries in separate .sql files makes them easier to lint, review and test.
Best practices
- Agree one style and enforce it with a tool. The best convention is the one everyone follows.
- Format on save, check in CI.
- Prefer explicit, named columns in
SELECTandINSERT. - Use CTEs to name steps in any query longer than a screen.
- Parenthesise boolean logic whenever
ANDandORare mixed. - Use parameterised queries, never string concatenation, for user input.
- Comment business rules, not syntax.
- Test queries on realistic data and review execution plans with
EXPLAINfor anything performance-sensitive. - Mind the dialect. Formatting rules are portable, but functions and syntax are not.
Comparison: style choices
| Choice | Option A | Option B | Advice |
|---|---|---|---|
| Keyword case | SELECT |
select |
Either; uppercase separates language from schema |
| Comma position | Trailing | Leading | Trailing is more common; leading eases commenting out the last line |
| Join style | Explicit JOIN |
Comma joins | Explicit, always |
| Subqueries | Nested | CTEs | CTEs for readability when queries grow |
| Aliases | Short | Descriptive | Short but meaningful |
None of these choices changes what the database does. They change how quickly a person can understand the query, so pick one set and keep it.
How a formatter reads your SQL
Knowing what a formatter does internally explains both its strengths and its occasional surprises. Almost every SQL formatter works in three stages: tokenise, classify, and print.
Stage one: tokenising
The tokeniser walks the text and splits it into tokens: words, numbers, operators, punctuation, string literals, quoted identifiers and comments. This stage is where most of the safety comes from. A quoted string such as 'SELECT * FROM x' becomes a single token, so the formatter never reindents or recases what is inside it. The same goes for a double-quoted identifier like "Order Total" and for comments.
The tokeniser must also know the dialect's quoting rules. PostgreSQL uses double quotes for identifiers and supports dollar-quoted strings such as $$ ... $$. MySQL uses backticks for identifiers. SQL Server uses square brackets. A tokeniser configured for the wrong dialect can misread where a string ends, and everything after that point is formatted as if it were code.
Stage two: classifying
Each word token is checked against lists of reserved words and function names. Top-level keywords such as SELECT, FROM and WHERE start new lines. Joins have their own list (LEFT JOIN, INNER JOIN, CROSS JOIN). Everything else is treated as an identifier, which is why a new keyword added to a database after the formatter was released may be left in its original case.
Parentheses add nesting. The formatter tracks depth so that a subquery inside brackets is indented one level deeper than the clause that contains it.
Stage three: printing
The printer emits tokens with the chosen indentation, keyword case and line-breaking rules. Because it works from tokens and never changes their text, apart from keyword case, the statement keeps its meaning. This is why a formatter can be trusted to change layout only, and also why it cannot fix a wrong query. It does not parse the full grammar the way a database does, so it will happily format invalid SQL.
A short history of SQL style
SQL began in the 1970s at IBM and was standardised by ANSI and ISO in the 1980s. Early SQL was typed into terminals and printed on green-bar paper, and the all-caps keyword habit comes from that era, when many systems and editors had no syntax highlighting at all. Uppercase keywords were the only visual cue available.
Modern editors colour keywords automatically, which is why lowercase style has gained supporters. The argument runs: if the editor already distinguishes keywords, capitals add noise. The counter-argument is that queries also appear in places without colour, such as logs, chat messages, pull-request diffs and slow-query reports, so capitalisation keeps working everywhere.
Neither side is wrong. What matters is that your team decides once and lets a tool enforce it.
Worked example: cleaning up a real-world query
Here is the sort of query a reporting tool might emit. It is correct, but nobody can read it:
select c.id,c.name,sum(oi.quantity*oi.unit_price) as revenue,count(distinct o.id) as orders from customers c inner join orders o on o.customer_id=c.id inner join order_items oi on oi.order_id=o.id where o.status in ('paid','shipped') and o.created_at>='2026-01-01' and (c.country='US' or c.country='CA') group by c.id,c.name having sum(oi.quantity*oi.unit_price)>5000 order by revenue desc limit 20;
We will clean it up in four passes. Doing it in passes, rather than all at once, shows which change gives which benefit.
Pass 1: layout
Give each clause its own line and each select item its own line:
SELECT
c.id,
c.name,
SUM(oi.quantity * oi.unit_price) AS revenue,
COUNT(DISTINCT o.id) AS orders
FROM customers AS c
INNER JOIN orders AS o
ON o.customer_id = c.id
INNER JOIN order_items AS oi
ON oi.order_id = o.id
WHERE o.status IN ('paid', 'shipped')
AND o.created_at >= DATE '2026-01-01'
AND (c.country = 'US' OR c.country = 'CA')
GROUP BY c.id, c.name
HAVING SUM(oi.quantity * oi.unit_price) > 5000
ORDER BY revenue DESC
LIMIT 20;
This is already reviewable. You can see three tables, two joins, three filters, and a threshold on revenue.
Pass 2: remove duplication
The expression SUM(oi.quantity * oi.unit_price) appears twice. If someone changes one copy, for instance to subtract a discount, and forgets the other, the HAVING clause quietly filters on a different number than the one displayed. A CTE removes the risk by computing the figure once:
WITH customer_revenue AS (
SELECT
o.customer_id,
SUM(oi.quantity * oi.unit_price) AS revenue,
COUNT(DISTINCT o.id) AS orders
FROM orders AS o
INNER JOIN order_items AS oi
ON oi.order_id = o.id
WHERE o.status IN ('paid', 'shipped')
AND o.created_at >= DATE '2026-01-01'
GROUP BY o.customer_id
)
SELECT
c.id,
c.name,
cr.revenue,
cr.orders
FROM customer_revenue AS cr
INNER JOIN customers AS c
ON c.id = cr.customer_id
WHERE cr.revenue > 5000
AND c.country IN ('US', 'CA')
ORDER BY cr.revenue DESC
LIMIT 20;
Notice what else improved. Order-level filters now sit next to the order tables, and customer-level filters sit next to the customer table. The country test became IN ('US', 'CA'), which removes the OR and its precedence trap. The final query is short enough to read in a few seconds.
Pass 3: confirm that nothing changed
A refactor is only safe if the result is the same. Run both versions against the same data and compare. A simple technique is to use set operations: SELECT ... EXCEPT SELECT ... in both directions. If both return no rows, the results match. Not every database spells it EXCEPT (Oracle uses MINUS), but the idea is portable.
Compare row counts and a few totals as well. A quick SUM(revenue) across the old and new results catches most slips.
Pass 4: record the intent
Add a one-line comment above the CTE saying why the status filter is paid and shipped and not refunded. That is the part a future reader cannot recover from the code.
Layout patterns for tricky constructs
Basic SELECT queries are easy to lay out. These constructs are where teams most often disagree, so it pays to settle them in advance.
Long CASE expressions
Put WHEN and THEN pairs on their own lines, indented under CASE, and align END with CASE:
SELECT
o.id,
CASE
WHEN o.total >= 1000 THEN 'large'
WHEN o.total >= 100 THEN 'medium'
ELSE 'small'
END AS size_band
FROM orders AS o;
Always include an ELSE. Without one, unmatched rows silently become NULL, which then flows into later calculations.
Window functions
Window clauses get long quickly. Break PARTITION BY and ORDER BY onto separate lines once the OVER clause no longer fits comfortably:
SELECT
e.id,
e.department_id,
e.salary,
RANK() OVER (
PARTITION BY e.department_id
ORDER BY e.salary DESC
) AS salary_rank
FROM employees AS e;
INSERT and UPDATE
List columns and values on matching lines so a reader can pair them by eye:
INSERT INTO customers (
name,
email,
country
)
VALUES (
'Ada Example',
'ada@example.test',
'GB'
);
UPDATE orders
SET
status = 'shipped',
shipped_at = CURRENT_TIMESTAMP
WHERE id = 42;
For UPDATE and DELETE, always keep the WHERE clause on its own line. A missing WHERE changes every row, and a clearly visible clause makes its absence obvious in review.
Subqueries
If a subquery has more than one clause, put the opening bracket at the end of the preceding line and the closing bracket on its own line, aligned with the line that opened it. Better still, promote it to a CTE once it grows past a few lines.
Long IN lists and VALUES blocks
For lists of literals, one value per line is the diff-friendly choice, while short lists of three or four items can stay inline. Large generated lists belong in a temporary table or a parameter array, not in the statement text.
Formatting and the query planner
Formatting never changes how a query runs, but readable layout makes planner problems easier to spot.
Reading EXPLAIN output against your query
When you run EXPLAIN on a formatted query, each plan node maps naturally to a line: a scan per table in FROM, a join node per JOIN, a filter per WHERE condition. With one condition per line you can tick them off against the plan and see which ones the database applied as index conditions and which it applied afterwards as filters.
Patterns that hide in plain sight
- Functions wrapped around indexed columns.
WHERE DATE(created_at) = '2026-03-01'often prevents a plain index oncreated_atfrom being used. A range such ascreated_at >= '2026-03-01' AND created_at < '2026-03-02'usually allows it. The exact behaviour depends on the database and on whether an expression index exists. - Leading wildcards.
LIKE '%smith'cannot use an ordinary B-tree index for a prefix search. - Implicit type conversion. Comparing a text column to a number can force a conversion on every row.
ORacross different columns. Some planners handle it well and others fall back to a scan, so test it.- Joins that multiply rows. Joining a one-to-many table before aggregating inflates sums. Aggregating inside a CTE first, as in the worked example, avoids it.
None of these show up in a one-line query. They are obvious when each condition has its own line.
CTE materialisation
Whether a CTE is computed once and stored, or merged into the outer query, depends on the engine. Recent PostgreSQL versions inline simple CTEs that are referenced once, and let you force either behaviour with MATERIALIZED and NOT MATERIALIZED. Other engines make their own choices. If a CTE-heavy query is slow, read the plan before rewriting it, and consult your database's documentation for its current rules.
Security considerations
Formatting and security overlap in a few places that are easy to overlook.
Formatting is not sanitising
A formatter cannot make unsafe SQL safe. If an application builds a statement by concatenating user input, the result may be nicely indented and still injectable. The fix is parameterised queries, where the statement text and the values travel separately and the driver never splices values into the SQL.
-- Safe: the value is a parameter, not part of the SQL text
SELECT id, name
FROM users
WHERE email = $1;
Placeholder syntax differs by driver: $1 in PostgreSQL drivers, ? in many others, and named placeholders such as :email in some. Identifiers such as table and column names cannot be parameters. When they must vary, choose them from a fixed allow-list in code.
Be careful where you paste queries
Queries often reveal table names, column names and sometimes literal values such as email addresses, tokens or customer identifiers. Before pasting into a public forum or an online service that uploads text, strip or replace sensitive literals. Prefer a formatter that runs locally in your browser, as the DevUtilX tool does, when the query came from production logs.
Logs and slow-query reports
Statement logging may record literal values. Many teams log the statement with placeholders and keep values out of logs, or mask them. A formatter is useful for reading these logs, but treat the formatted output with the same care as the original.
Editor, pre-commit and CI setup
A good setup has three layers: format as you type, check before you commit, and enforce in CI. Each layer catches what the previous one missed.
In the editor
Most editors can run a formatter on save, through an extension or a language server. Set the dialect explicitly rather than relying on detection, because a wrong guess produces odd results with dialect-specific syntax. Share the editor settings through the repository so newcomers get the same behaviour on day one.
Before the commit
A pre-commit hook runs the formatter or linter on staged .sql files. Using a framework such as pre-commit keeps the hook definition in the repository. A typical entry points at a sqlfluff hook and names the dialect, and the exact hook ids and versions are documented in the tool's own repository. Keep the hook fast: lint only changed files, not the whole tree.
In CI
CI is the backstop for anyone whose hook is missing or skipped. Run the same command locally and in CI, in check mode so it fails and reports rather than rewriting files. Print the diff on failure so the author can see exactly what to change.
Introducing a formatter to an old codebase
Reformatting a large existing repository creates a huge diff and wrecks git blame. The usual approach is:
- Agree the style and commit the configuration first.
- Run the formatter over everything in one dedicated commit that changes nothing else.
- Record that commit in a
.git-blame-ignore-revsfile, and configure Git to use it withblame.ignoreRevsFile, so blame skips it. - Turn on the CI check afterwards, so new work stays formatted.
Do not mix formatting changes with logic changes in the same commit. Reviewers cannot tell what really changed.
Linting: rules that go beyond layout
A formatter fixes whitespace. A linter also flags habits. Typical rules include:
- Unqualified columns in queries with several tables.
SELECT *in production code.- Implicit aliasing.
- Inconsistent keyword capitalisation.
- Trailing whitespace and missing final newline.
- Reserved words used as identifiers.
- Mixed
JOINstyles.
Start with a short rule set and expand it. A linter that fires hundreds of warnings on day one gets disabled. It is better to enable a few rules that the team agrees on, fix the violations, and add more later.
Some rules cannot be fixed automatically because they change meaning, such as replacing SELECT * with a list of columns. Treat those as review prompts, not as auto-fixes.
Templated SQL and embedded queries
Real projects rarely keep SQL as plain text. Three situations deserve specific advice.
SQL inside application strings
Multi-line string literals work well in most languages. Keep the query in a constant, format it the same way as a .sql file, and indent it consistently with surrounding code. Many languages offer a raw or template string syntax that avoids escaping quotes. Where possible, load queries from files so linters and formatters can treat them as SQL.
Jinja and other templating
Analytics tools often embed template tags such as {{ ref('orders') }} or {% if ... %} in SQL. A plain SQL formatter may choke on these or move them in surprising ways. Use a formatter that understands your templating, or configure the linter's templater. As a rule, keep tags on their own lines and keep template logic out of the middle of expressions.
Query builders and ORMs
When the SQL is generated, you format the output for reading, not the generator's code. Enable query logging in development, copy the statement, and format it. This is the best use of a quick tool such as the SQL Formatter: paste, read, and understand what your ORM actually asked for.
Troubleshooting formatting problems
| Symptom | Likely cause | Fix |
|---|---|---|
| Formatter reports a syntax error on valid SQL | Wrong dialect selected | Choose the matching dialect, such as PostgreSQL or MySQL |
| Everything after one string is badly indented | Unbalanced quote or unsupported quoting style | Check for a stray ', or a dollar-quoted or backtick-quoted token the tool does not know |
| Keywords in new syntax not capitalised | Keyword missing from the tool's list | Update the tool, or leave those words as they are |
| Template tags moved or broken | Templating not understood | Use a templating-aware formatter or exclude those files |
| Formatter and linter fight over the same line | Conflicting rules | Align configuration, or let one tool own layout |
| Huge diff after formatting | First run over old code | Do a dedicated formatting commit and ignore it in blame |
| Comments end up in odd places | Inline comments attached to the wrong token | Move comments to their own lines above the code |
| Result runs differently than the original | Formatting touched literal data, or the original was invalid | Compare against the original and report the bug to the tool |
A useful first step for any formatting oddity is to reduce the query to the smallest piece that still misbehaves. The problem is almost always one construct, such as a comment, a vendor extension or an unusual string.
Comments, hints and special syntax
Comments deserve a few specific rules, because they are where formatters differ most.
- Line comments (
-- text) run to the end of the line. Join two lines into one by accident and the second half becomes a comment. This is why formatters preserve line breaks after--comments. - Block comments (
/* text */) can span lines. Some databases support nesting, others do not, so avoid nesting. - Optimizer hints are written as specially formatted comments in some databases, for example
/*+ ... */in Oracle and MySQL. Changing the text inside them changes behaviour, and a formatter must leave them alone. After formatting a query that contains hints, confirm they are still in place. - Commented-out code should not be committed. Version control remembers the old version.
A team checklist for SQL conventions
Use this list when writing or reviewing a team standard. It turns the debates above into decisions.
- Keyword case: uppercase or lowercase.
- Indentation: four spaces, two spaces or tabs.
- Comma position: trailing or leading.
- Maximum line length, if any.
- Alias rules: when to use them, how long they may be, and whether
ASis required. - Join style and the order of
ONconditions. - How
ANDandORare bracketed. - When a subquery must become a CTE.
- Naming: case, singular or plural, prefixes and suffixes for keys and dates.
- Where queries live: files, constants or generated code.
- Which formatter and linter, which dialect and which version.
- How migrations are named and ordered.
Write the answers in one short document next to the configuration file. The shorter it is, the more likely people are to read it.
Accessibility and readability for every reader
Readable SQL helps colleagues who read code in different ways. Developers using screen readers hear code line by line, so one logical item per line is easier to follow than a single long line. Developers who use large text or narrow windows benefit from lines that do not scroll sideways. People who are dyslexic often find consistent capitalisation and generous whitespace easier to scan. Meaningful aliases help everyone, and they matter most to readers who cannot skim visually.
Avoid conveying meaning through colour alone in documentation. If you highlight a line in a shared query, also mark it with a comment.
Case study: a query that nobody could change
Imagine a team with a monthly finance report built from one 200-line query. It was pasted into a dashboard tool, edited in place for two years, and had grown filters in four different styles. When a discount rule changed, an engineer updated one of three copies of the revenue expression, and the report totals stopped matching the invoicing system for a month.
The fix was not clever. The team formatted the query, which made the three copies of the expression visible. They moved it into one CTE, added a comment explaining each business rule, and saved the query in the repository as a .sql file with a test that compared totals to a known sample. The next rule change touched one line.
The lesson is that formatting is often the first step of a repair. It does not solve anything on its own, but it exposes duplication, hidden precedence and dead filters that were invisible when the query was dense.
Migrations and schema files
Data definition statements have their own layout needs. A CREATE TABLE reads best with one column per line, the type aligned or at least consistently spaced, and constraints grouped at the end:
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'pending',
created_at TIMESTAMP NOT NULL,
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers (id)
);
Name constraints and indexes explicitly. Generated names are hard to refer to later, and they differ between environments, which makes a later DROP awkward.
Never edit a migration that has already run anywhere shared. Add a new migration instead. Format old migrations only in the dedicated formatting commit described earlier, and only if your migration tool does not checksum file contents, since some tools refuse to run when a previously applied file has changed.
Keep one logical change per migration, and put a short comment at the top saying what it does and why. Future readers use these files as the history of the schema.
Reviewing SQL in a pull request
Consistent layout lets reviewers spend attention on the questions that matter. A short checklist helps:
- Does every join have an
ONcondition, and is it on the right columns? - Could a join multiply rows before an aggregate?
- Is
NULLhandled in comparisons and inNOT IN? - Do
UPDATEandDELETEstatements have aWHEREclause? - Are values parameters, not concatenated text?
- Is there an index to support the new filter or join, and has the plan been checked on realistic data?
- Is the result deterministic? A
LIMITwithoutORDER BYreturns arbitrary rows.
A diff tool that highlights changes within a line is handy when a formatting change and a logic change land together, though it is better to keep them apart.
FAQ
Should SQL keywords be uppercase?
It is a common convention because it separates keywords from identifiers, and style guides recommend it. Lowercase is also acceptable. Consistency matters more than the choice.
Does formatting affect query performance?
No. The database parses the statement and ignores whitespace and keyword case. Performance depends on the query logic, indexes and data.
Are CTEs slower than subqueries?
It depends on the database and version. Some engines treat CTEs as optimisation fences, while others inline them. Check the execution plan for hot queries.
Leading or trailing commas?
Trailing commas are the more common style. Leading commas make it easier to comment out the last column without fixing punctuation. Choose one and apply it consistently.
Can a formatter break my query?
A correct formatter changes only whitespace and keyword case. Still, run the formatted query, especially if it contains dialect-specific syntax the tool may not fully support.
Do I need different formatting for different databases?
The layout rules are the same, but the syntax is not. Choose a formatter or linter that supports your dialect.
How long should a line of SQL be?
There is no standard. Many teams use a limit between 80 and 120 characters, which keeps queries readable in side-by-side diffs. Short lines also encourage one condition per line.
Should I align column types and aliases into neat columns?
Alignment looks tidy, but it means a change to one name can reindent every line, which pollutes diffs. If you align, let a tool do it. Otherwise use a single space and accept the ragged edge.
What indentation width is best?
Four spaces is common in SQL style guides, and two spaces is popular in teams that embed SQL in indentation-heavy languages. Tabs also work. Pick one and put it in the formatter configuration.
Can I format only part of a file?
Most formatters and editors can format a selection. Be careful with fragments, since a partial statement may be tokenised differently from the full one. It is safer to format whole statements.
Is INNER JOIN better than plain JOIN?
They are identical to the database. Writing INNER makes the join type explicit and matches LEFT and CROSS, which some teams find clearer. Choose one form and keep it.
Does a formatter help with SELECT *?
No. It will lay out SELECT * neatly, but it will not expand it, because that needs the schema. A linter can flag it, and your editor or database client can often expand the star into a column list for you.
Should test data and seed scripts follow the same style?
Yes. Seed scripts are read when something is wrong, which is exactly when you need clarity. Apply the same formatter and keep literals one row per line.
Conclusion
Readable SQL is a habit built from small, consistent decisions: capitalised keywords, one clause per line, one column per line, explicit joins, parenthesised logic and CTEs for long queries. Automate the layout so nobody has to think about it, and spend the saved effort on correctness and performance. For a fast clean-up of any query, try the SQL Formatter. To keep exploring, read our guides on HTML formatting and JSON formatting.
.jpg)
.jpg)
.jpg)