SQL Formatting Best Practices

Why SQL Formatting Matters

SQL is often written once but read many times—by teammates, future maintainers, and your future self. Well-formatted SQL is easier to understand, debug, and modify. It reduces errors during code review and makes query optimization more straightforward.

Unlike most programming languages, SQL has no enforced formatting. This flexibility often leads to inconsistent, hard-to-read code. Establishing formatting conventions creates clarity and professionalism.

General Principles

• Consistency: Pick a style and stick to it throughout your codebase
• Readability: Optimize for human understanding, not compactness
• One concept per line: Each clause or condition on its own line
• Meaningful indentation: Show logical structure through whitespace

Keyword Case

Use consistent casing for SQL keywords. Common conventions:

UPPERCASE Keywords (Traditional)

SELECT user_id, email, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESC;

Pros: Keywords stand out clearly. Common in documentation and legacy codebases.

lowercase keywords (Modern)

select user_id, email, created_at
from users
where status = 'active'
order by created_at desc;

Pros: Easier to type, less visual noise. Gaining popularity in modern tools.

Either is acceptable—what matters is consistency. Most teams use uppercase keywords for better visual distinction.

Formatting SELECT Statements

Simple Queries

SELECT id, name, email
FROM users
WHERE status = 'active';

Complex Queries: Column per Line

SELECT
    u.id,
    u.first_name,
    u.last_name,
    u.email,
    u.created_at,
    COUNT(o.id) AS order_count,
    SUM(o.total) AS lifetime_value
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.status = 'active'
    AND u.created_at >= '2024-01-01'
GROUP BY u.id, u.first_name, u.last_name, u.email, u.created_at
HAVING COUNT(o.id) > 0
ORDER BY lifetime_value DESC
LIMIT 100;

Leading Comma Style

Some teams prefer commas at the start of lines for easier commenting:

SELECT
    u.id
    , u.first_name
    , u.last_name
    -- , u.phone  -- temporarily excluded
    , u.email
FROM users u;

JOIN Formatting

SELECT
    o.id AS order_id,
    o.total,
    u.name AS customer_name,
    p.name AS product_name
FROM orders o
INNER JOIN users u
    ON u.id = o.user_id
LEFT JOIN order_items oi
    ON oi.order_id = o.id
LEFT JOIN products p
    ON p.id = oi.product_id
WHERE o.status = 'completed';

JOIN Best Practices

• Always use explicit JOIN syntax, not comma-separated tables in FROM
• Specify join type (INNER, LEFT, RIGHT)—don't rely on defaults
• Put ON conditions immediately after the join
• Use short, meaningful table aliases
• Indent complex ON conditions

SELECT *
FROM orders, users
WHERE orders.user_id = users.id

SELECT o.*, u.*
FROM orders o
INNER JOIN users u
    ON u.id = o.user_id

WHERE Clause Formatting

SELECT *
FROM orders
WHERE status = 'pending'
    AND created_at >= '2024-01-01'
    AND (
        total > 1000
        OR customer_type = 'vip'
    )
    AND shipping_country IN ('US', 'CA', 'UK');

Guidelines

• Put AND/OR at the beginning of continuation lines
• Indent conditions under WHERE
• Use parentheses to clarify complex logic
• Align similar conditions when it improves readability

Subqueries and CTEs

Inline Subquery

SELECT
    u.name,
    u.email,
    (
        SELECT COUNT(*)
        FROM orders o
        WHERE o.user_id = u.id
    ) AS order_count
FROM users u;

CTEs (Common Table Expressions)

CTEs improve readability for complex queries:

WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(total) AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_TRUNC('month', created_at)
),
growth_calc AS (
    SELECT
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
        revenue - LAG(revenue) OVER (ORDER BY month) AS growth
    FROM monthly_revenue
)
SELECT
    month,
    revenue,
    growth,
    ROUND(growth * 100.0 / NULLIF(prev_revenue, 0), 2) AS growth_pct
FROM growth_calc
ORDER BY month;

CTE Best Practices

• Give CTEs descriptive names
• Use CTEs instead of deeply nested subqueries
• Separate multiple CTEs with blank lines
• Each CTE should do one logical thing

INSERT, UPDATE, DELETE

INSERT

INSERT INTO users (
    first_name,
    last_name,
    email,
    created_at
)
VALUES (
    'John',
    'Doe',
    'john@example.com',
    NOW()
);

UPDATE

UPDATE users
SET
    status = 'inactive',
    updated_at = NOW(),
    deactivated_by = 'system'
WHERE last_login < '2023-01-01'
    AND status = 'active';

DELETE

DELETE FROM sessions
WHERE expires_at < NOW()
    AND user_id NOT IN (
        SELECT id FROM users WHERE is_admin = true
    );

Comments

-- Single line comment

/*
 * Multi-line comment
 * Explaining complex business logic
 */

SELECT
    user_id,
    -- Exclude test accounts from metrics
    CASE
        WHEN email LIKE '%@test.com' THEN 'test'
        ELSE 'production'
    END AS account_type
FROM users;

When to Comment

• Explain why, not what—the SQL shows what
• Document business rules and edge cases
• Note performance considerations
• Mark temporary fixes or TODOs

Naming Conventions

• Tables: Lowercase, plural, snake_case (user_accounts)
• Columns: Lowercase, snake_case (created_at, user_id)
• Primary keys: id or table_name_id
• Foreign keys: referenced_table_id (user_id)
• Booleans: Prefix with is_, has_, can_
• Timestamps: Suffix with _at (created_at, deleted_at)
• Aliases: Short but meaningful (u for users, oi for order_items)

Quick Reference

Format Your SQL

Use our SQL formatter to automatically beautify and standardize your queries.