Writing Readable, Maintainable SQL: Formatting Standards & CTEs

SQL is a declarative language designed to express what data you want rather than how to retrieve it. Yet in production codebases, SQL files frequently resemble dense, unformatted walls of text spanning hundreds of lines. A messy SQL query is more than an aesthetic nuisance: it conceals Cartesian product joins, masks redundant subqueries, makes pull request reviews excruciating, and drastically inflates debugging time during production incidents.

Author Note: SQL queries become difficult to maintain when joins, filters, and nested conditions are all packed into a single line. I prefer formatting queries so that each major operation is easy to identify and the relationships between tables are clear. Readable SQL makes reviews and debugging easier, although performance still needs to be evaluated separately.

1. The Five Iron Rules of SQL Formatting

Rule 1: Uppercase All SQL Keywords

Always capitalize language keywords (SELECT, FROM, WHERE, JOIN, ON, GROUP BY, HAVING, ORDER BY, LIMIT). This establishes instant optical separation between SQL syntactic operators and your application-specific table and column identifiers.

Rule 2: Place Major Clauses on New Left-Aligned Lines

Major clause keywords must begin on their own lines. Indent projection columns and predicate conditions by 2 or 4 spaces underneath their parent clause keyword.

Rule 3: One Projected Column Per Line with Trailing Commas

In the SELECT clause, never list multiple columns on a single line. Placing each attribute on its own line ensures that Git diffs clearly highlight added or removed columns without masking changes in adjacent lines.

Rule 4: Always Use Explicit JOIN Syntax with Explicit ON Predicates

Never use legacy ANSI SQL-89 comma joins (FROM users u, orders o WHERE u.id = o.user_id). Comma joins invite accidental cross-joins if a predicate is forgotten. Always declare explicit INNER JOIN, LEFT JOIN, or FULL OUTER JOIN.

Rule 5: Use Meaningful, Consistent Table Aliases

Avoid single-letter aliases like a, b, c when joining multiple entities. Use memorable mnemonic abbreviations (e.g. usr for users, ord for orders, pmt for payments).

2. The Modern Transformation: Subqueries vs Common Table Expressions (CTEs)

The single most powerful technique for improving SQL clarity is replacing deeply nested subqueries with Common Table Expressions (CTEs) using the WITH statement. CTEs transform an inside-out, hard-to-follow subquery tree into a top-to-bottom procedural pipeline.

When queries nest subqueries within subqueries three levels deep, reviewers must read from the innermost parenthetical block outward. With CTEs, the mental model mirrors clean functional programming: each CTE represents an immutable intermediate dataset with an explicit descriptive name.

3. Step-by-Step Worked Refactoring Example

Examine this unreadable raw query commonly logged by ORMs or drafted in database administration consoles:

BEFORE: Unformatted, Nested Subquery Blob

select c.id,c.name,o.total from customers c left join (select customer_id,sum(amount) as total from orders where created_at >= '2026-01-01' and status in ('completed','shipped') group by customer_id having sum(amount) > 1000) o on c.id=o.customer_id where c.country='US' and c.deleted_at is null order by o.total desc limit 25;

Now compare it to the same analytical business logic refactored into modular CTEs following clean formatting standards:

AFTER: Clean, Modular CTE Pipeline

WITH qualifying_orders AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spent_usd
    FROM
        orders
    WHERE
        status IN ('completed', 'shipped')
        AND created_at >= '2026-01-01'
    GROUP BY
        customer_id
    HAVING
        SUM(amount) > 1000
),

active_us_customers AS (
    SELECT
        id AS customer_id,
        name AS customer_name,
        email
    FROM
        customers
    WHERE
        country = 'US'
        AND deleted_at IS NULL
)

SELECT
    c.customer_id,
    c.customer_name,
    c.email,
    COALESCE(o.total_spent_usd, 0) AS total_spent_usd
FROM
    active_us_customers c
    INNER JOIN qualifying_orders o ON c.customer_id = o.customer_id
ORDER BY
    total_spent_usd DESC
LIMIT 25;

Notice how any engineer on your team can understand each logical stage in under five seconds without having to mentally parse nested parenthesis depths.

4. The LEFT JOIN Nullification Trap

One of the most subtle logic bugs in SQL occurs when a developer writes a LEFT JOIN to preserve optional customer records, but inadvertently converts it into an INNER JOIN by filtering the joined table in the WHERE clause:

-- BUG: Converts LEFT JOIN to INNER JOIN

SELECT c.name, o.order_date

FROM customers c

LEFT JOIN orders o ON c.id = o.customer_id

WHERE o.status = 'shipped'; -- Filters out customers with NULL orders!

-- CORRECT: Place the condition in the ON clause

SELECT c.name, o.order_date

FROM customers c

LEFT JOIN orders o ON c.id = o.customer_id AND o.status = 'shipped';

5. Formatting Complex Window Functions and CASE Expressions

Analytical window functions (ROW_NUMBER(), RANK(), LEAD(), LAG()) are notoriously prone to horizontal sprawl. Format the OVER (...) clause and CASE WHEN conditionals with dedicated indentation:

SELECT
    employee_id,
    department_id,
    salary,
    CASE
        WHEN salary >= 150000 THEN 'Executive'
        WHEN salary >= 100000 THEN 'Senior'
        ELSE 'Core'
    END AS compensation_tier,
    DENSE_RANK() OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
    ) AS salary_rank_in_dept
FROM
    employees;

6. Defensive Aggregations: Handling NULLs and Zero Division

Writing clean SQL is not solely about line breaks; it is also about writing self-documenting defensive expressions. In production analytical queries, unexpected NULL values or division-by-zero errors will abort long-running data warehouse jobs or corrupt downstream reporting dashboards.

-- FRAGILE: Crashes on zero completed orders

SELECT total_revenue / total_orders AS average_order_value FROM daily_metrics;

-- ROBUST: Uses NULLIF to return NULL safely when total_orders is 0

SELECT ROUND(total_revenue / NULLIF(total_orders, 0), 2) AS average_order_value_usd FROM daily_metrics;

Wrapping denominators in NULLIF(denominator, 0) turns zero into a harmless NULL, allowing the calculation to complete without throwing an unhandled runtime arithmetic exception.

7. The Pull Request Review Checklist for SQL

Before approving any SQL migration or analytical script in GitHub or GitLab, run through this five-point review checklist:

Index Alignment: Are all predicates in the WHERE and JOIN ... ON clauses supported by composite indexes on the underlying database tables?
No Wildcard Projections: Are all columns explicitly named instead of using SELECT *, preventing network bandwidth bloat and schema drift issues?
Consistent Casing: Are all SQL keywords capitalized and all table/column identifiers formatted in lowercase snake_case?
Deterministic Ordering: If a LIMIT clause is present, is it paired with a deterministic ORDER BY clause containing a unique primary key to prevent jittery pagination?
Modular CTE Structure: Are subqueries extracted into named CTEs so junior team members can follow the transformation pipeline effortlessly?

8. Recommended Database Utilities on Black Claaw

Format, convert, and test your data pipelines in seconds using our client-side tools:

Writing Readable, Maintainable SQL: Practical Standards for Fast Code Reviews and Fewer Bugs