SQL Query Formatting Best Practices

Write readable, maintainable SQL queries with consistent style. Covers capitalization, indentation, JOIN formatting, subqueries, and naming conventions.

By Belqassim Djafer · Published · Updated

Why SQL Formatting Matters

SQL is among the most commonly written languages in software engineering yet receives the least attention in terms of style. Unformatted SQL is notoriously hard to debug, review, and maintain. A well-formatted query communicates intent clearly and makes performance bottlenecks easier to identify.

Keyword Capitalization

The most visible formatting choice is whether to capitalize SQL keywords. There are two common conventions:

-- Uppercase keywords (traditional, widely used)
SELECT u.id, u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.active = TRUE
ORDER BY o.total DESC;

-- Lowercase keywords (growing in popularity, matches IDE highlighting)
select u.id, u.name, o.total
from users u
join orders o on o.user_id = u.id
where u.active = true
order by o.total desc;

Either style is acceptable — what matters is consistency. Most style guides (SQLStyle.guide, GitLab) recommend uppercase keywords to distinguish them from identifiers.

Indentation and Line Breaks

Break long queries into multiple lines, aligning keywords:

-- Bad: unreadable one-liner
SELECT u.id, u.name, u.email, o.id AS order_id, o.total FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE u.active = TRUE AND u.created_at > '2024-01-01' ORDER BY u.name ASC LIMIT 100;

-- Good: aligned, readable
SELECT
  u.id,
  u.name,
  u.email,
  o.id    AS order_id,
  o.total AS order_total
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
WHERE u.active = TRUE
  AND u.created_at > '2024-01-01'
ORDER BY u.name ASC
LIMIT 100;

Recommended approach: each major clause (SELECT, FROM, JOIN, WHERE, GROUP BY, ORDER BY) starts a new line at column 0. Column lists and conditions are indented 2–4 spaces.

Naming Conventions

Object Convention Example
Tables snake_case, plural users, order_items
Columns snake_case first_name, created_at
Primary keys id or table_id id, user_id
Foreign keys {referenced_table}_id user_id, product_id
Indexes idx_{table}_{column} idx_users_email
Booleans is_ or has_ prefix is_active, has_subscription

Avoid reserved words as column names (name, value, date, order, type). Use more specific names or quote them.

SELECT Clause Best Practices

Avoid SELECT *

-- Avoid: returns all columns, breaks with schema changes
SELECT * FROM users;

-- Better: explicit columns
SELECT id, name, email, created_at FROM users;

Using explicit columns makes queries self-documenting, prevents accidentally returning sensitive columns (passwords, tokens), and improves query planner performance.

Use Aliases for Clarity

SELECT
  u.id          AS user_id,
  u.first_name  AS name,
  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
GROUP BY u.id, u.first_name;

JOIN Formatting

Each JOIN gets its own line, with the ON clause indented:

SELECT
  o.id,
  u.name   AS customer,
  p.name   AS product,
  oi.quantity
FROM orders o
INNER JOIN users u
  ON u.id = o.user_id
INNER JOIN order_items oi
  ON oi.order_id = o.id
INNER JOIN products p
  ON p.id = oi.product_id
WHERE o.status = 'completed';

Always specify JOIN type explicitly (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN). Bare JOIN is an implicit INNER JOIN — being explicit avoids ambiguity.

CTEs vs. Subqueries

Common Table Expressions (CTEs) are significantly more readable than nested subqueries for complex logic:

-- Subquery approach: hard to read
SELECT u.name, totals.total
FROM users u
JOIN (
  SELECT user_id, SUM(total) AS total
  FROM orders
  WHERE status = 'completed'
  GROUP BY user_id
) totals ON totals.user_id = u.id
WHERE totals.total > 1000;

-- CTE approach: readable, named steps
WITH completed_orders AS (
  SELECT
    user_id,
    SUM(total) AS total
  FROM orders
  WHERE status = 'completed'
  GROUP BY user_id
),
high_value_customers AS (
  SELECT user_id
  FROM completed_orders
  WHERE total > 1000
)
SELECT u.id, u.name, co.total
FROM users u
JOIN completed_orders co ON co.user_id = u.id
WHERE u.id IN (SELECT user_id FROM high_value_customers);

WHERE Clause Formatting

Align conditions with the leading AND/OR:

WHERE u.active = TRUE
  AND u.role IN ('admin', 'editor')
  AND u.created_at BETWEEN '2024-01-01' AND '2024-12-31'
  AND (
    u.country = 'FR'
    OR u.country = 'BE'
  );

Comments in SQL

-- Single-line comment: explain the why, not the what

/*
  Multi-line comment for complex query explanations.
  Explain business logic, not just syntax.
*/

SELECT
  order_id,
  -- Exclude cancelled and refunded orders from revenue
  SUM(CASE WHEN status NOT IN ('cancelled', 'refunded') THEN total ELSE 0 END) AS net_revenue
FROM orders
GROUP BY order_id;

SQL Formatters

Manually reformatting SQL is tedious. Use our SQL Formatter to automatically apply consistent formatting to any SQL query. The SQL Validator catches syntax errors before you run queries against a real database.

Conclusion

Consistent SQL formatting pays dividends during code reviews, debugging sessions, and when onboarding new team members. Adopt a style guide, use a formatter for automated consistency, and prefer CTEs over deeply nested subqueries. Clean SQL is not just aesthetics — it directly enables faster, safer query reviews.

Related Tools