Complete Guide to SQL Formatting and Style
Why SQL formatting matters
SQL is often written under time pressure — debugging a production issue, composing a one-off analysis, or copying from a log file. The result is frequently unreadable: no indentation, mixed keyword case, everything on one line. Formatted SQL is easier to read, review, and debug. When a WHERE clause spans multiple lines with clear indentation, logical errors (wrong AND/OR grouping, missing conditions) become obvious at a glance. When JOIN conditions are each on their own line, it is easy to spot a missing JOIN or an ON clause on the wrong table. Formatting does not change what the query does — it changes how quickly a human can understand it.
SQL style guide fundamentals
The most common SQL style conventions: (1) Keywords in UPPERCASE (SELECT, FROM, WHERE, JOIN, GROUP BY, ORDER BY, HAVING, LIMIT). (2) Table and column names in lowercase with underscores (snake_case). (3) Each major clause on its own line: SELECT on line 1, FROM on line 2, WHERE on line 3, etc. (4) Indent the column list and WHERE conditions by one level (2 or 4 spaces). (5) Use explicit JOIN syntax (INNER JOIN, LEFT JOIN) rather than comma-separated tables in the FROM clause — the comma syntax is a legacy form that is harder to read and reason about. (6) Alias tables with short, meaningful names (u for users, o for orders). (7) End each statement with a semicolon, even when not strictly required by the database.
SQL dialect differences that affect formatting
SQL is not fully standardized — each database adds its own syntax and quoting rules. MySQL uses backticks for identifiers (``SELECT `user_id` FROM `users```) and supports AUTO_INCREMENT. PostgreSQL uses double quotes for identifiers ("user_id") and uses SERIAL or GENERATED AS IDENTITY for auto-increment. SQL Server (T-SQL) uses square brackets for identifiers ([user_id]) and uses IDENTITY for auto-increment. SQLite is very permissive and supports multiple quoting styles. BigQuery uses backticks like MySQL but has different type names and functions. These differences matter when formatting: a formatter configured for MySQL may mangle PostgreSQL-specific syntax and vice versa. Always select the correct dialect.
Common SQL anti-patterns to recognize
SELECT *: selecting all columns prevents the query planner from using index-only scans and makes the query fragile when the table schema changes. Always name the columns you need. Implicit JOINs: writing FROM a, b WHERE a.id = b.a_id instead of FROM a JOIN b ON a.id = b.a_id mixes filtering and joining logic, making queries harder to read and reason about. N+1 queries: running one query to get a list, then one query per item in the list. Use a JOIN or subquery instead. Missing indexes: filtering on unindexed columns causes full table scans. Check EXPLAIN output. Functions on indexed columns in WHERE: WHERE YEAR(created_at) = 2024 prevents index use; use WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01' instead. String concatenation: building SQL by concatenating user input causes SQL injection. Always use parameterized queries.
How to use EXPLAIN and ANALYZE
EXPLAIN (or EXPLAIN PLAN in some databases) shows the query execution plan — how the database intends to retrieve the data. EXPLAIN ANALYZE (PostgreSQL) or EXPLAIN FORMAT=JSON ANALYZE (MySQL 8.0+) actually runs the query and shows both the planned and actual costs. Key things to look for: Seq Scan (full table scan) on large tables usually indicates a missing index. Nested Loop with a large outer set may indicate an N+1 pattern. Hash Join is often good; Nested Loop is good when the inner set is small. High 'rows removed by filter' indicates many rows being scanned and discarded — an index would help. Sort operations on large result sets can be expensive — check if you can add an index on the ORDER BY column. Always run ANALYZE on tables before interpreting EXPLAIN output, as outdated statistics lead to misleading plans.
SQL in application code: best practices
Parameterized queries (prepared statements) are mandatory for any user-supplied data — never concatenate strings to build SQL. In Python, use cursor.execute('SELECT * FROM users WHERE id = %s', (user_id,)). In Node.js with pg, use client.query('SELECT * FROM users WHERE id = $1', [userId]). In Java, use PreparedStatement. ORMs like SQLAlchemy, ActiveRecord, and Sequelize use parameterized queries by default, but raw query escapes are possible if you use the raw() or literal() methods carelessly. Store complex, reused queries in dedicated files or a query builder — not inline strings scattered throughout the codebase. Add comments to queries that have non-obvious performance implications or that implement complex business logic. Include the query in a code review whenever it touches a high-traffic table.
SQL formatting in CI/CD pipelines
Automating SQL formatting enforcement requires a formatter that can run in your CI environment. For Node.js projects, sql-formatter-cli can be installed and run as a lint step. For Python projects, sqlfluff is the most capable SQL linter and formatter — it supports multiple dialects, can check style rules (not just formatting), and integrates with pre-commit. For dbt projects, sqlfmt is the standard formatter for Jinja-templated SQL models. For PostgreSQL specifically, pg_format is a C-based formatter that can be installed as a binary. The general approach: add the formatter to your pre-commit hooks, fail CI if any .sql file is not formatted, and document the project's SQL style in your contributing guide so new developers know what to expect.