SQL formatting: indentation and line-break conventions for readability

SQL is permissive about whitespace, so formatting drives most of its readability. This article surveys common conventions.

Without formatting

SELECT u.id, u.name, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE u.active = TRUE AND u.created_at > '2024-01-01' GROUP BY u.id, u.name HAVING COUNT(o.id) > 0 ORDER BY order_count DESC;

A single dense line is hard to scan and edit, and review diffs become noisy.

Left-aligned keyword style (mainstream)

Keywords flush left, columns and tables indented:

SELECT
    u.id,
    u.name,
    COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.active = TRUE
  AND u.created_at > '2024-01-01'
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 0
ORDER BY order_count DESC;

Strengths:

• Each clause (SELECT, FROM, WHERE…) is visible at a glance.
• Adding/removing columns produces clean diffs.
• AND/OR continuation lines indent visibly.

ANSI style (right-aligned keywords)

Keywords right-aligned, entries starting from the SELECT column:

  SELECT u.id,
         u.name,
         COUNT(o.id) AS order_count
    FROM users u
    LEFT JOIN orders o ON o.user_id = u.id
   WHERE u.active = TRUE
     AND u.created_at > '2024-01-01'
GROUP BY u.id, u.name
  HAVING COUNT(o.id) > 0
ORDER BY order_count DESC;

• Columns line up neatly.
• Painful to type and re-align (column-name length changes break it).

Left-aligned has won.

Per-clause rules

SELECT

One column per line:

SELECT
    u.id,
    u.name,
    u.email
FROM users u

Trailing vs leading commas — preference varies:

-- leading commas
SELECT
    u.id
  , u.name
  , u.email

-- trailing commas (more common)
SELECT
    u.id,
    u.name,
    u.email

Leading commas reduce comma errors when reordering rows; trailing reads more like English.

JOIN

One JOIN per line:

FROM users u
LEFT JOIN orders o ON o.user_id = u.id
LEFT JOIN products p ON p.id = o.product_id

For complex ON clauses:

LEFT JOIN orders o
    ON o.user_id = u.id
   AND o.deleted_at IS NULL

WHERE

AND / OR at line start:

WHERE u.active = TRUE
  AND u.created_at > '2024-01-01'
  AND u.role IN ('admin', 'editor')

When mixing OR, parenthesize:

WHERE u.active = TRUE
  AND (
       u.role = 'admin'
    OR u.role = 'editor'
  )

AND binds tighter than OR — explicit parens prevent surprises.

Case (UPPER vs lower)

Traditional uppercase keywords:

SELECT * FROM users WHERE id = 1;

Modern lowercase style:

select * from users where id = 1;

Either works if you’re consistent. Pick one as a team.

Quoting identifiers

Standard SQL / PostgreSQL — double quotes:

SELECT "user_id" FROM "users";

MySQL — backticks:

SELECT `user_id` FROM `users`;

Skip quoting unless your name collides with a reserved word.

Subqueries

Nest by indenting another level:

SELECT u.name
FROM users u
WHERE u.id IN (
    SELECT user_id
    FROM orders
    WHERE total > 1000
);

CTEs are usually clearer than nested subqueries:

WITH high_spenders AS (
    SELECT user_id
    FROM orders
    WHERE total > 1000
)
SELECT u.name
FROM users u
JOIN high_spenders h ON h.user_id = u.id;

Function calls

Break long argument lists:

SELECT
    COALESCE(
        u.preferred_name,
        u.full_name,
        'Unknown'
    ) AS display_name
FROM users u;

Short ones inline:

SELECT COALESCE(u.name, 'Anon') FROM users u;

CASE expressions

WHENs vertical:

SELECT
    CASE
        WHEN u.age < 18 THEN 'minor'
        WHEN u.age < 65 THEN 'adult'
        ELSE 'senior'
    END AS age_group
FROM users u;

Indent width

• 4 spaces — readable but wide.
• 2 spaces — compact, good for deep nesting.
• Tabs — environment-dependent, avoid for shared codebases.

A formatter (dbt, SQLFluff, etc.) standardizes the team’s choice.

Comments

Line and block comments:

-- single-line

/*
 * multi-line
 * intent / context
 */

SELECT u.name
FROM users u
WHERE u.active = TRUE  -- exclude deleted
  AND u.created_at > '2024-01-01';  -- new users only

Comment the why, not the what.

Numeric vs string literals

• Strings — single quotes — 'hello'
• Numbers — bare — 42
• NULL — keyword — NULL (no quotes)

'NULL' is the string “NULL”, not SQL NULL — a classic bug.

Auto-formatters

Hand-formatting doesn’t scale:

• SQLFluff — Python linter / formatter.
• pgFormatter — PostgreSQL-focused.
• sql-formatter — JavaScript implementation.
• Prettier (sql-formatter plugin) — integrates with Prettier.
• dbt — dbt format for model files.

Wire CI to format on PR so diffs only show real changes.

Summary

• Left-aligned keyword style is the modern default.
• One clause per group of lines, AND/OR at line start.
• Trailing commas are conventional (leading is a defensible alternative).
• CTEs read better than nested subqueries.
• Use a formatter to enforce team style.

To format SQL on the fly, the SQL formatter on this site handles common dialects.