ToolSink
Back to blog

SQL Formatting Best Practices for Clean Code

ToolSink Team

When working with data, writing a query that returns the correct results is only half the battle. The other half is ensuring that your query is readable, maintainable, and easy for your teammates (and your future self) to understand. SQL is a highly flexible language, which is both a blessing and a curse. Because the engine doesn't care about whitespace or capitalization, it is incredibly easy to write sprawling, monolithic queries that are practically impossible to decipher.

Clean code isn't just for software engineering; it's a critical requirement for data engineering and analytics. In this comprehensive guide, we'll dive into the best practices for formatting SQL code to keep your queries pristine, readable, and professional.

Core Principles of SQL Formatting

Establishing a baseline of core formatting rules is the first step toward a unified, clean codebase. Whether you are querying a transactional database or a massive data warehouse, these foundational principles apply.

1. Capitalization: The Age-Old Debate

One of the most noticeable aspects of any SQL query is how capitalization is handled. While there are multiple schools of thought, the most widely accepted standard is:

  • Uppercase SQL Keywords: Always write reserved words and functions in uppercase. This includes SELECT, FROM, WHERE, JOIN, GROUP BY, SUM(), and COUNT().
  • Lowercase Identifiers: Keep table names, column names, and variables in lowercase, ideally using snake_case (e.g., customer_id, transaction_date).

This visual contrast makes it instantly obvious what part of the syntax is native to SQL and what represents your specific data model.

-- Bad
select customer_name, count(order_id) from sales.orders group by customer_name;

-- Good
SELECT 
    customer_name, 
    COUNT(order_id) AS total_orders
FROM sales.orders
GROUP BY 
    customer_name;

2. Indentation and Line Breaks

Whitespace is your best friend when writing SQL. A dense block of text is intimidating and hard to debug. Proper indentation establishes a visual hierarchy.

  • One Column Per Line: In your SELECT clause, put each column on its own line. This makes it trivial to comment out a single column during debugging and keeps git diffs clean.
  • Indent Clauses: Sub-clauses should be indented relative to their parent keywords. Use 2 or 4 spaces consistently (avoid tabs, as they render differently across IDEs).
  • Trailing Commas vs. Leading Commas: While leading commas (e.g., , column_name) have a cult following for making it easier to comment out the last line, trailing commas are the industry standard. Stick to trailing commas unless your team has explicitly agreed otherwise.

3. Meaningful and Consistent Aliasing

Aliasing is crucial for readability, especially when dealing with multiple joins or aggregated columns.

  • Always use AS for columns: While SQL allows you to omit the AS keyword when aliasing a column, including it makes your intention explicit.
  • Descriptive Table Aliases: Avoid arbitrary single-letter aliases like a, b, and c in large queries. If you are joining the employees table, alias it as emp or employees. If you must use single letters, ensure they represent the table name (e.g., c for customers).
-- Bad
SELECT a.name, b.amount 
FROM customers a 
JOIN orders b ON a.id = b.customer_id;

-- Good
SELECT 
    c.name AS customer_name, 
    o.amount AS order_amount
FROM customers AS c
JOIN orders AS o 
    ON c.id = o.customer_id;

Structuring Complex Queries

As your questions become more complex, your queries will naturally grow. How you structure these large queries determines whether they remain comprehensible.

4. Embrace Common Table Expressions (CTEs)

If there is one practice that will instantly elevate your SQL, it is using Common Table Expressions (CTEs) instead of nested subqueries.

Nested subqueries force the reader to read from the inside out, jumping around the code to understand the flow of data. CTEs, introduced with the WITH clause, allow you to define temporary result sets sequentially at the top of your query. This reads like a linear narrative: first we get the active users, then we get their recent orders, and finally we join them together.

-- The CTE Approach
WITH active_users AS (
    SELECT 
        user_id,
        email
    FROM users
    WHERE status = 'active'
),

recent_orders AS (
    SELECT 
        user_id,
        SUM(total_amount) AS total_spent
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY 
        user_id
)

SELECT 
    u.email,
    o.total_spent
FROM active_users AS u
JOIN recent_orders AS o 
    ON u.user_id = o.user_id;

Name your CTEs descriptively. A CTE named cte1 is just as unhelpful as a table alias named a.

5. Proper JOIN Formatting

Joins are where many queries turn into a tangled mess. Keep them clean by standardizing how you write your join conditions.

  • Line Breaks for ON: Always put the ON condition on a new line, indented under the JOIN.
  • Multiple Conditions: If your join has multiple conditions, put each AND on a new line and align them with the ON.
  • Explicit JOIN Types: Always be explicit. Write INNER JOIN or LEFT JOIN instead of just JOIN.
-- Good JOIN Formatting
SELECT 
    e.employee_name,
    d.department_name
FROM employees AS e
LEFT JOIN departments AS d
    ON e.department_id = d.department_id
    AND d.is_active = true;

Filtering and Grouping

The WHERE, GROUP BY, and ORDER BY clauses are the final pieces of the puzzle. They control exactly what data is returned and how it is presented.

6. Writing Clean WHERE Clauses

When filtering data, clarity of logic is paramount.

  • One Condition Per Line: Just like columns in a SELECT clause, put each condition on its own line.
  • Use Parentheses Liberally: When mixing AND and OR operators, never rely on the reader to remember the order of operations. Always wrap logical groupings in parentheses to make the behavior explicit.
-- Good WHERE Formatting
SELECT 
    product_id,
    product_name
FROM inventory
WHERE stock_level < 10
    AND (category = 'Electronics' OR category = 'Accessories')
    AND is_discontinued = false;

7. Explicit GROUP BY and ORDER BY

Many SQL dialects allow you to group or order by the column index (e.g., GROUP BY 1, 2). While this saves keystrokes, it is a dangerous habit. If someone later adds a new column to the beginning of the SELECT list, the GROUP BY clause will silently break or group by the wrong data.

Always write out the explicit column names or aliases in your GROUP BY and ORDER BY clauses. It makes the code more robust and easier to read without having to count columns.

Tooling and Automation

While knowing these rules is important, enforcing them manually is tedious. The best teams rely on automation to keep their SQL clean.

8. Use SQL Formatters and Linters

Integrate a SQL linter into your workflow. Tools like SQLFluff or built-in formatters in platforms like dbt can automatically check your code against a defined style guide and even auto-fix many issues.

Setting up a linter in your Continuous Integration (CI) pipeline ensures that no poorly formatted code ever makes its way into your production repositories. This ends debates in code reviews and lets the team focus on the logic of the query rather than the indentation.

Conclusion

Writing clean SQL is an investment in your team's productivity. By adhering to consistent capitalization, utilizing whitespace, embracing CTEs, and being explicit in your joins and groupings, you transform SQL from a messy script into professional-grade code.

Start small by agreeing on a style guide with your team, implement an automated linter, and watch as your codebase becomes significantly easier to read, debug, and maintain. Clean code isn't an accident—it's a deliberate practice.

Back to blog