Tips SQL formatting style guide database

SQL Formatting Best Practices: A Style Guide With Examples

By ClickSolveTools Editorial TeamUpdated 12 min read

Good SQL formatting comes down to a few rules: put each major clause on its own line, list one column per line, indent continuation lines four spaces, write explicit JOIN ... ON, pick one keyword case and keep it, and move multi-step logic into named CTEs. SQL ignores whitespace and keyword case, so none of this changes what a query returns. It changes how fast a teammate can read, review and debug it.

No official layout standard exists, so the rules below come from the conventions that public style guides share, with the disagreements called out: Simon Holywell's SQL Style Guide, Matt Mazur's SQL style guide, dbt's style guide and the default rules of the SQLFluff linter. The recommended layouts use PostgreSQL syntax and pass sqlfluff lint with the settings shown near the end; the formatting before-and-after pairs were run against a test database to confirm they return identical rows, and the anti-pattern examples note where a database behaves differently. For a quick cleanup of your own query, try our SQL Formatter.

Before and After: Why Layout Matters

Here is a query as it often appears in a production codebase:

select u.id,u.name,u.email,count(o.id) as order_count,sum(o.total) as lifetime_value from users u join orders o on u.id=o.user_id where u.created_at>='2026-01-01' and o.status='completed' group by u.id,u.name,u.email having sum(o.total)>1000 order by lifetime_value desc limit 50;

And the same query, formatted:

SELECT
    u.id,
    u.name,
    u.email,
    COUNT(o.id) AS order_count,
    SUM(o.total) AS lifetime_value
FROM users AS u
INNER JOIN orders AS o
    ON u.id = o.user_id
WHERE
    u.created_at >= '2026-01-01'
    AND o.status = 'completed'
GROUP BY
    u.id,
    u.name,
    u.email
HAVING SUM(o.total) > 1000
ORDER BY lifetime_value DESC
LIMIT 50;

Both return the same rows. The second version shows the columns, the join, the filters and the aggregation at a glance, so when a bug report says the totals are wrong, you can find the clause to check in seconds.

Keyword Capitalization

The most visible choice is whether keywords such as SELECT and WHERE are uppercase or lowercase. The major style guides do not agree:

Guide Keyword case Notes
Simon Holywell, SQL Style Guide UPPERCASE Reserved keywords in uppercase; aligns keywords to a "river" down the middle of the query
Matt Mazur, SQL style guide lowercase Calls lowercase just as readable and easier on the shift key; left-aligns keywords
dbt, SQL style guide lowercase Field names, keywords and function names all lowercase; four-space indents
SQLFluff, default rule CP01 consistent Either case passes as long as it is not mixed; configurable to upper or lower

Recommendation: choose one and enforce it with a linter, because consistency matters more than the choice. This guide uses UPPERCASE because keywords stand out from column names in plain text, such as in an email or a pull request diff. Lowercase is equally valid and is the dbt community's habit. Avoid mixed Title Case.

Indentation and Line Breaks

The core rule: each major clause starts on a new line, and continuation lines are indented. Four spaces is the dbt recommendation and the SQLFluff default. Holywell's "river" layout lines keywords up instead; it reads well, but Mazur's guide advises against it because it is cumbersome to do by hand.

SELECT List

Put each column on its own line. Adding, removing or reordering a column then changes one line, which keeps version-control diffs small:

-- BAD: hard to see every column, messy diffs
SELECT u.id, u.name, u.email, u.created_at, u.plan_type
FROM users AS u

-- GOOD: one column per line
SELECT
    u.id,
    u.name,
    u.email,
    u.created_at,
    u.plan_type
FROM users AS u

JOIN Clauses

Each join gets its own line, with the ON condition indented beneath it. When ON has more than one condition, put the first one on its own line as well, which is the SQLFluff default:

SELECT
    o.id,
    u.name,
    p.name AS product_name
FROM orders AS o
INNER JOIN users AS u
    ON o.user_id = u.id
LEFT JOIN products AS p
    ON
        o.product_id = p.id
        AND p.active = TRUE

Write INNER JOIN instead of a bare JOIN. They mean the same thing, but the dbt guide, Mazur's guide and SQLFluff's default rule AM05 all prefer the explicit form. Never list tables separated by commas in FROM. A forgotten WHERE condition turns a comma join into a cross join that returns every combination of rows.

-- BAD: comma join, one forgotten WHERE condition makes it a cross join
SELECT o.id, u.name
FROM orders AS o, users AS u
WHERE o.user_id = u.id

-- GOOD: explicit join with the condition next to it
SELECT
    o.id,
    u.name
FROM orders AS o
INNER JOIN users AS u
    ON o.user_id = u.id

WHERE Clause

Put each condition on its own line with the logical operator at the start:

SELECT u.id
FROM users AS u
WHERE
    u.status = 'active'
    AND u.created_at >= '2026-01-01'
    AND u.plan_type IN ('pro', 'enterprise')
    AND u.email NOT LIKE '%@test.com'

A leading AND lets you comment out any condition except the first one while debugging, with no other edit:

SELECT u.id
FROM users AS u
WHERE
    u.status = 'active'
    -- AND u.created_at >= '2026-01-01'  -- disabled while debugging
    AND u.plan_type IN ('pro', 'enterprise')

Commas

Trailing commas, at the end of the line, are the SQLFluff default and the recommendation in the dbt and Mazur guides, so they suit most tooling. Leading commas make it marginally easier to comment out the last column. Either is fine if the whole team uses the same one and the linter is set to match.

CASE Expressions

Put each WHEN on its own indented line and close with END AS alias. As a rule of thumb, when a CASE grows past about ten branches, a lookup table joined to the query is easier to maintain.

SELECT
    u.name,
    CASE
        WHEN u.lifetime_value >= 10000 THEN 'platinum'
        WHEN u.lifetime_value >= 5000 THEN 'gold'
        WHEN u.lifetime_value >= 1000 THEN 'silver'
        ELSE 'bronze'
    END AS customer_tier
FROM users AS u

CTEs vs. Subqueries

A common table expression (CTE, written with WITH) names a step of the query so the final SELECT reads top to bottom. Mazur's guide goes as far as "avoid subqueries", and dbt's guide recommends one logical unit of work per CTE. Compare the same logic both ways:

Subquery Version

SELECT
    user_segments.segment,
    COUNT(*) AS user_count,
    AVG(user_segments.total_spent) AS avg_spent
FROM (
    SELECT
        u.id,
        SUM(o.total) AS total_spent,
        CASE
            WHEN SUM(o.total) >= 5000 THEN 'high_value'
            WHEN SUM(o.total) >= 1000 THEN 'medium_value'
            ELSE 'low_value'
        END AS segment
    FROM users AS u
    INNER JOIN orders AS o
        ON u.id = o.user_id
    WHERE
        o.status = 'completed'
        AND o.created_at >= '2026-01-01'
    GROUP BY u.id
) AS user_segments
GROUP BY user_segments.segment
ORDER BY avg_spent DESC;

CTE Version

WITH user_spending AS (
    SELECT
        u.id,
        SUM(o.total) AS total_spent
    FROM users AS u
    INNER JOIN orders AS o
        ON u.id = o.user_id
    WHERE
        o.status = 'completed'
        AND o.created_at >= '2026-01-01'
    GROUP BY u.id
),

user_segments AS (
    SELECT
        id,
        total_spent,
        CASE
            WHEN total_spent >= 5000 THEN 'high_value'
            WHEN total_spent >= 1000 THEN 'medium_value'
            ELSE 'low_value'
        END AS segment
    FROM user_spending
)

SELECT
    segment,
    COUNT(*) AS user_count,
    AVG(total_spent) AS avg_spent
FROM user_segments
GROUP BY segment
ORDER BY avg_spent DESC;

The CTE version reads like a recipe: total the spending, assign a segment, then aggregate. You can also run user_spending alone to check the intermediate numbers. Both versions returned identical results on our test data.

When a subquery is still fine: a short lookup such as WHERE id IN (SELECT ...) or a correlated subquery that refers to the outer row is often clearer inline. Use a CTE when there are several steps or when you need the same derived result more than once.

Are CTEs Slower?

It depends on the database, because each one treats a CTE differently:

Database How a CTE is executed
PostgreSQL 12 and later Inlined into the parent query if it is non-recursive, has no side effects and is referenced once. Referenced more than once, it is computed once (materialized) unless you write NOT MATERIALIZED. Before version 12, a CTE was always evaluated first.
MySQL 8.x Can be merged into the outer query like a derived table, or materialized once for the whole query even if referenced several times. A recursive CTE is always materialized.
SQL Server Results are not materialized. Each outer reference to the CTE runs its query again, so a CTE used twice runs twice; a temporary table is the documented alternative for reuse.

So a CTE used once is usually planned like the equivalent subquery in PostgreSQL and MySQL, while a CTE referenced several times on SQL Server can cost more. For any query where speed matters, read the plan (EXPLAIN ANALYZE in PostgreSQL) instead of guessing.

Five Anti-Patterns to Avoid

1. SELECT *

-- BAD: returns every column, and both tables have id and created_at
SELECT *
FROM users AS u
INNER JOIN orders AS o
    ON u.id = o.user_id

-- GOOD: only the columns you need, each one named
SELECT
    u.id,
    u.name,
    o.id AS order_id,
    o.total
FROM users AS u
INNER JOIN orders AS o
    ON u.id = o.user_id

SELECT * returns columns you do not need, breaks code that depends on column order when the table changes, and hides name clashes in joins. It is fine for quick exploration. In application code, views and scheduled reports, list the columns.

2. Unqualified Columns

-- BAD: which table does "name" come from?
SELECT name, total
FROM users
INNER JOIN orders ON users.id = orders.user_id

-- GOOD: every column says where it comes from
SELECT
    u.name,
    o.total
FROM users AS u
INNER JOIN orders AS o
    ON u.id = o.user_id

Whenever a query touches more than one table, qualify every column with its table or alias. Alias style is one place the guides differ: Holywell's guide wants AS on every alias, while dbt's and Mazur's guides prefer full table names over short aliases in join conditions. Short aliases such as u and o are fine for short queries; use longer names when several tables share a first letter.

3. Comparing Mismatched Types

-- BAD: phone_number is a VARCHAR column and the literal is a number
SELECT u.id FROM users AS u WHERE u.phone_number = 5551234567

-- GOOD: compare text to text
SELECT u.id
FROM users AS u
WHERE u.phone_number = '5551234567'

MySQL compares a string column with a number as floating-point numbers, which means it cannot use the index on that column, and strings such as '5551234567x' also match. PostgreSQL rejects the query with an "operator does not exist" error instead. Either way, write the literal in the column's type.

4. NOT IN With a Subquery

-- RISKY: if blocked_users.user_id holds even one NULL,
-- NOT IN never evaluates to true and the query returns no rows
SELECT u.id, u.name
FROM users AS u
WHERE u.id NOT IN (SELECT b.user_id FROM blocked_users AS b)

-- SAFE: NOT EXISTS ignores NULLs in the subquery
SELECT
    u.id,
    u.name
FROM users AS u
WHERE NOT EXISTS (
    SELECT 1
    FROM blocked_users AS b
    WHERE b.user_id = u.id
)

When the subquery returns a NULL, NOT IN cannot evaluate to true and the query returns zero rows. On our test table, one NULL in blocked_users took the result from 199 of 200 users to none, while NOT EXISTS still returned 199.

5. Functions on Indexed Columns and BETWEEN on Timestamps

-- BAD: the function hides created_at from a plain index on the column
SELECT o.id FROM orders AS o WHERE YEAR(o.created_at) = 2026

-- GOOD: a half-open range can use the index
SELECT o.id
FROM orders AS o
WHERE
    o.created_at >= '2026-01-01'
    AND o.created_at < '2027-01-01'

A plain index on created_at cannot be used when the column is wrapped in a function, because the database sees a different expression. The fix is a half-open range with >= and <. In SQLite's query plan, the wrapped version was a full scan and the range was an index search. A function-based (expression) index on the same function is the alternative, but it only helps queries that use that exact function. YEAR() exists in MySQL and SQL Server; PostgreSQL spells it EXTRACT(YEAR FROM created_at). Avoid BETWEEN for timestamps too: it includes both ends, so a range ending at midnight on the 8th also returns rows stamped exactly midnight on the 8th.

Formatting a Complex Query Step by Step

  1. State the goal. Write a one-line comment saying what the query returns.
  2. List the logical steps. For example: get completed orders, join the products, total by month and category.
  3. Write each step as a CTE with a name that says what it does, such as completed_orders.
  4. Format each CTE on its own, so each one is a short, simple query.
  5. Keep the final SELECT short, because it only combines the CTEs.
-- Monthly revenue by product category for active customers
WITH completed_orders AS (
    SELECT
        o.id AS order_id,
        DATE_TRUNC('month', o.created_at) AS order_month
    FROM orders AS o
    INNER JOIN users AS u
        ON o.user_id = u.id
    WHERE
        o.status = 'completed'
        AND u.status = 'active'
),

order_products AS (
    SELECT
        co.order_month,
        co.order_id,
        p.category,
        oi.quantity * oi.unit_price AS line_total
    FROM completed_orders AS co
    INNER JOIN order_items AS oi
        ON co.order_id = oi.order_id
    INNER JOIN products AS p
        ON oi.product_id = p.id
)

SELECT
    order_month,
    category,
    SUM(line_total) AS revenue,
    COUNT(DISTINCT order_id) AS order_count
FROM order_products
GROUP BY
    order_month,
    category
ORDER BY
    order_month DESC,
    revenue DESC;

DATE_TRUNC is PostgreSQL syntax, and other databases spell it differently. LIMIT works in PostgreSQL and MySQL; the SQL standard spelling is FETCH FIRST n ROWS ONLY, and SQL Server uses TOP or OFFSET ... FETCH.

SQL Formatting Checklist

Enforcing a Style With a Linter

Agreeing on a style is easy. Keeping it is what a linter is for. SQLFluff is an open-source linter and auto-fixer that supports many dialects, including PostgreSQL, MySQL, T-SQL (SQL Server), BigQuery and Snowflake. The dialect is required:

pip install sqlfluff

sqlfluff lint query.sql --dialect postgres
sqlfluff fix query.sql --dialect postgres

Out of the box it wants consistent keyword case, trailing commas, four-space indents and AS on aliases. To require uppercase keywords, add a .sqlfluff file to your project:

[sqlfluff]
dialect = postgres

[sqlfluff:rules:capitalisation.keywords]
capitalisation_policy = upper

Run it in CI so style changes never reach review. When a formatting-only change touches many lines, commit it on its own and compare the before and after with our Diff Checker to confirm that only whitespace and keyword case moved. For a quick one-off cleanup without installing anything, our SQL Formatter lets you pick 2 spaces, 4 spaces or a tab and UPPERCASE, lowercase or preserved keywords; review its output, because it does not know your team's conventions. To try a query on realistic rows, the Fake Data Generator can export records as SQL. More utilities are in the Developer Tools hub, and the same habits apply to formatting JSON.

Frequently Asked Questions

Should SQL keywords be uppercase or lowercase?

Either works, as long as the whole team uses the same one. Simon Holywell's SQL Style Guide says to use uppercase for reserved keywords, while Matt Mazur's guide and dbt's style guide both say lowercase. SQLFluff's default only requires that keyword case is consistent. The database treats both the same, so pick one, put it in a SQLFluff config and let the linter enforce it.

Are CTEs slower than subqueries?

Not by rule. PostgreSQL 12 and later inline a CTE that is referenced once, MySQL can merge a CTE into the outer query or materialize it once, and SQL Server does not materialize CTE results, so a CTE referenced twice runs twice. Check the query plan for any query where speed matters.

Should I use trailing or leading commas in a column list?

Trailing commas are the SQLFluff default and the recommendation in the dbt and Mazur style guides, so they work with most tooling. Leading commas make it slightly easier to comment out the last column. Choose one, use it everywhere and set your linter to match.

Does SQL formatting affect query performance?

No. Whitespace, line breaks and keyword case do not change what a query means or how the optimizer plans it. Performance problems come from the query itself, such as a function wrapped around an indexed column, a type mismatch in a comparison or a CTE that SQL Server re-runs at every reference.

Which SQL formatter or linter should I use?

SQLFluff is an open-source linter and auto-fixer for many dialects, including PostgreSQL, MySQL, T-SQL, BigQuery and Snowflake. Install it with pip install sqlfluff, then run sqlfluff lint query.sql --dialect postgres to check a file or sqlfluff fix to rewrite it. For a quick one-off cleanup, paste the query into our SQL Formatter and choose the indent and keyword case.

Sources

Figures and rules in this guide were checked against these primary references in September 2026: