SQL Best Practices: Write Clean, Readable Queries

Why SQL Style Matters

A SQL query can be written in a hundred ways. The one that works and the one that is maintainable are often different. Well-formatted SQL is faster to review in pull requests, easier to debug, and less likely to contain subtle bugs.

This guide covers formatting conventions, naming standards, and structural patterns used by experienced database developers.

Capitalization Conventions

The most universally accepted convention: SQL keywords in UPPERCASE, identifiers in lowercase.

-- Good
SELECT u.id, u.email, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.active = true
GROUP BY u.id, u.email
HAVING COUNT(o.id) > 0
ORDER BY order_count DESC
LIMIT 20;

-- Bad (hard to scan)
select u.id, u.email, count(o.id) as order_count from users u left join orders o on u.id = o.user_id where u.active = true group by u.id, u.email having count(o.id) > 0 order by order_count desc limit 20;

Indentation and Line Breaks

Each major clause starts on a new line. Continuation lines should align:

SELECT
    u.id,
    u.name,
    u.email,
    p.plan_name,
    COUNT(o.id) AS total_orders,
    SUM(o.amount) AS lifetime_value
FROM users u
    INNER JOIN subscriptions s ON s.user_id = u.id
    INNER JOIN plans p ON p.id = s.plan_id
    LEFT JOIN orders o ON o.user_id = u.id
WHERE
    u.created_at >= '2024-01-01'
    AND u.status = 'active'
    AND s.expires_at > NOW()
GROUP BY u.id, u.name, u.email, p.plan_name
ORDER BY lifetime_value DESC;

Naming Conventions

Consistent naming prevents confusion:

CTEs Over Subqueries

Common Table Expressions (CTEs) make complex queries readable:

-- With CTEs (readable)
WITH active_users AS (
    SELECT id, email FROM users WHERE status = 'active'
),
recent_orders AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    WHERE created_at >= NOW() - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT au.email, ro.order_count
FROM active_users au
JOIN recent_orders ro ON ro.user_id = au.id
ORDER BY ro.order_count DESC;

-- Equivalent nested subquery (harder to follow)
SELECT u.email, o.order_count
FROM (SELECT id, email FROM users WHERE status = 'active') u
JOIN (SELECT user_id, COUNT(*) AS order_count FROM orders
      WHERE created_at >= NOW() - INTERVAL '30 days'
      GROUP BY user_id) o ON o.user_id = u.id
ORDER BY o.order_count DESC;

Using EXPLAIN and Query Optimization

Before shipping a query to production:

-- Check the execution plan
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 123;

-- Look for:
-- Seq Scan on large tables (bad) → needs an index
-- Nested Loop on large sets (slow) → consider Hash Join
-- High actual time values → optimization opportunity

Window Functions

Window functions are powerful but often misused. Format them clearly:

SELECT
    id,
    user_id,
    amount,
    ROW_NUMBER() OVER (
        PARTITION BY user_id
        ORDER BY created_at DESC
    ) AS purchase_rank,
    SUM(amount) OVER (
        PARTITION BY user_id
        ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders;

Formatting Tools

Formatting SQL by hand is tedious. Use HeoLab's SQL Formatter to instantly beautify queries across MySQL, PostgreSQL, SQLite, T-SQL, and PL/SQL dialects. Simply paste, choose your dialect, and copy the result.

Conclusion

Good SQL style is an investment. It reduces bugs (readability catches mistakes), speeds up code reviews, and makes complex queries maintainable by your future self. Start with the basics — consistent capitalization and line breaks — then gradually adopt CTEs and window functions.