How to Write Clean, Readable SQL

Why SQL Formatting Matters

SQL is often treated as an afterthought — queries built up by string concatenation, crammed onto one line, or copy-pasted without thought for readability. But SQL is code, and it deserves the same care as any other code in your codebase. A poorly formatted query is hard to review, hard to debug, and hard to optimize. A well-formatted query reveals its own logic.

These conventions are not arbitrary preferences. They have evolved from decades of database development to minimize errors, make structure visible, and enable efficient code review.

Keyword Casing

Use uppercase for SQL keywords (SELECT, FROM, WHERE, JOIN, ON, GROUP BY, ORDER BY, HAVING, LIMIT) and lowercase for identifiers (table names, column names, aliases). The visual contrast makes it immediately clear what is SQL syntax and what is your schema.

-- Good
SELECT u.id, u.email FROM users u WHERE u.active = 1;

-- Bad: all lowercase, harder to parse
select u.id, u.email from users u where u.active = 1;

Indentation and Line Breaks

Each clause goes on its own line. Selected columns are indented and each gets its own line when there are more than two or three. Aliases use AS explicitly for clarity.

SELECT
    u.id,
    u.email,
    u.created_at,
    p.name AS plan_name,
    COUNT(o.id) AS order_count
FROM users u
JOIN plans p
    ON p.id = u.plan_id
LEFT JOIN orders o
    ON o.user_id = u.id
WHERE
    u.active = 1
    AND u.created_at >= '2026-01-01'
GROUP BY
    u.id,
    u.email,
    u.created_at,
    p.name
HAVING COUNT(o.id) > 0
ORDER BY u.created_at DESC
LIMIT 100;

WHERE Clause Clarity

Put each condition on its own line. Put the logical operator (AND, OR) at the beginning of the line — not the end. This makes it easy to comment out individual conditions during debugging:

WHERE
    u.active = 1
    AND u.country = 'ID'
    -- AND u.plan_id = 2   ← easy to disable temporarily
    AND u.created_at >= '2026-01-01'

This is one of the most practical SQL formatting habits you can adopt. Commenting out a trailing AND breaks the syntax; commenting out a leading AND is always safe.

JOIN Formatting

Align the ON clause under the JOIN with one additional level of indentation. For multi-condition joins, format each condition on its own line:

JOIN order_items oi
    ON oi.order_id = o.id
    AND oi.deleted_at IS NULL

Always be explicit about join type: INNER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN. Never write bare JOIN — it is technically an inner join, but the ambiguity costs readers a moment of thought every time.

Prefer CTEs Over Nested Subqueries

Common Table Expressions (CTEs) defined with WITH are dramatically more readable than deeply nested subqueries. They let you build complex queries from named, readable steps:

WITH active_users AS (
    SELECT id, email, plan_id
    FROM users
    WHERE active = 1
      AND deleted_at IS NULL
),
high_value AS (
    SELECT user_id, SUM(amount) AS total_spent
    FROM orders
    WHERE status = 'completed'
    GROUP BY user_id
    HAVING SUM(amount) > 1000000
)
SELECT
    u.email,
    u.plan_id,
    hv.total_spent
FROM active_users u
JOIN high_value hv
    ON hv.user_id = u.id
ORDER BY hv.total_spent DESC;

Each CTE is a named, testable step. You can run each CTE independently during development by adding a simple SELECT * FROM cte_name. Nested subqueries offer no such debugging affordance.

Comments in SQL

Use single-line comments (--) for brief explanations and block comments (/* ... */) for longer documentation. Comment the why, not the what — the SQL itself explains what it does; comments should explain business rules and non-obvious decisions:

-- Exclude soft-deleted records; our ORM does not filter these automatically
WHERE u.deleted_at IS NULL

/* 
  Using LEFT JOIN here instead of INNER JOIN because users without any orders
  are still valid accounts and should appear in the report with 0 order_count.
*/

Naming Conventions

Be consistent with table and column naming. The most common conventions:

• Tables: plural, snake_case (users, order_items, product_categories)
• Columns: snake_case (first_name, created_at, is_active)
• Boolean columns: prefix with is_ or has_ (is_active, has_verified_email)
• Timestamp columns: suffix with _at (created_at, deleted_at)
• Foreign keys: referenced_table_singular_id (user_id, plan_id)

Tooling

Use PureFormatter's SQL Formatter to instantly clean up any query before committing it or sharing it with teammates. For ongoing development, configure your database client (DBeaver, DataGrip, TablePlus) to format on save.