SQL Formatting Best Practice: Panduan Gaya untuk Query yang Mudah Dibaca

Messy SQL is hard to debug, painful to review, and a nightmare to maintain. Whether you work solo or on a team, consistent SQL formatting transforms unreadable walls of text into clear, logical statements. This guide covers every aspect of SQL style -- from keyword casing and indentation to CTE formatting and naming conventions -- with before/after examples you can apply immediately.

1. Why SQL Formatting Matters

SQL is often treated as a "write-once" language, but production queries get read dozens of times. Proper formatting pays dividends in three key areas:

• Readability -- Well-formatted SQL lets you scan the query structure at a glance. You can instantly see which tables are joined, what filters apply, and what columns are selected.
• Code Review -- Reviewers can focus on logic instead of deciphering structure. Diff views become meaningful when each clause occupies its own line.
• Debugging -- When a query returns wrong results, formatted SQL lets you isolate each clause and test it independently. Finding a misplaced AND or a wrong JOIN condition takes seconds instead of minutes.
• Maintenance -- Six months from now, you (or your teammate) will need to modify that query. Formatted SQL makes modifications safe and predictable.

2. Keywords: UPPERCASE vs lowercase

The two most common conventions for SQL keywords:

Style A: UPPERCASE Keywords (Most Common)

Uppercase keywords visually separate SQL structure from data identifiers. This is the most widely adopted convention and recommended for teams.

SELECT
    user_id,
    first_name,
    last_name,
    email
FROM users
WHERE is_active = TRUE
ORDER BY created_at DESC
LIMIT 100;

Style B: lowercase Keywords

Some developers prefer lowercase for a more modern feel. This works well with syntax highlighting in modern editors.

select
    user_id,
    first_name,
    last_name,
    email
from users
where is_active = true
order by created_at desc
limit 100;

Verdict: Pick one and enforce it across your team. UPPERCASE is the industry default and works best in plain-text contexts (logs, Slack, emails) where syntax highlighting is unavailable.

3. Indentation: Tabs vs Spaces, One Clause Per Line

The golden rule: one major clause per line. This makes diffs clean and clauses easy to comment out.

• Use consistent indentation (2 or 4 spaces; avoid tabs for portability).
• Each major keyword (SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT) starts on a new line at the same indentation level.
• Sub-clauses (JOIN conditions, AND/OR filters) are indented one level deeper.
• Align related elements vertically when it improves readability.

-- Each major clause on its own line, sub-clauses indented
SELECT
    u.user_id,
    u.email,
    COUNT(o.order_id) AS order_count
FROM users AS u
LEFT JOIN orders AS o
    ON u.user_id = o.user_id
WHERE u.is_active = TRUE
    AND u.created_at >= '2024-01-01'
GROUP BY
    u.user_id,
    u.email
HAVING COUNT(o.order_id) > 5
ORDER BY order_count DESC
LIMIT 20;

4. SELECT: One Column Per Line, Trailing vs Leading Commas

Listing one column per line makes it trivial to add, remove, or reorder columns. The comma placement debate has two camps:

Trailing Commas (More Common)

SELECT
    user_id,
    first_name,
    last_name,
    email,
    created_at
FROM users;

Leading Commas

SELECT
    user_id
    , first_name
    , last_name
    , email
    , created_at
FROM users;

Leading commas make it easier to spot missing commas and to comment out the last column. However, trailing commas are more natural to read and are the more common choice.

5. JOIN: Formatting Multi-Table Queries

JOINs are where formatting pays the biggest dividends. A well-formatted JOIN block clearly shows the relationship between tables.

• Each JOIN starts on a new line at the same level as FROM.
• The ON clause is indented under its JOIN.
• Multi-condition ON clauses use AND on separate indented lines.
• Always use explicit JOIN syntax (never comma-separated tables in FROM).

SELECT
    o.order_id,
    o.order_date,
    c.customer_name,
    p.product_name,
    oi.quantity,
    oi.unit_price
FROM orders AS o
INNER JOIN customers AS c
    ON o.customer_id = c.customer_id
INNER JOIN order_items AS oi
    ON o.order_id = oi.order_id
INNER JOIN products AS p
    ON oi.product_id = p.product_id
    AND p.is_active = TRUE
WHERE o.order_date >= '2024-01-01'
ORDER BY o.order_date DESC;

6. WHERE: AND/OR Alignment, Subquery Indentation

WHERE clauses can grow complex quickly. Proper formatting keeps the logic tree visible.

• Each AND/OR starts on a new line, aligned with the first condition or indented under WHERE.
• Use parentheses explicitly for mixed AND/OR logic.
• Indent subqueries as a nested block.
• Place the operator (AND/OR) at the beginning of the line, not the end.

-- Simple WHERE with AND
WHERE u.is_active = TRUE
    AND u.email_verified = TRUE
    AND u.created_at >= '2024-01-01'

-- Mixed AND/OR with explicit parentheses
WHERE u.is_active = TRUE
    AND (
        u.role = 'admin'
        OR u.role = 'moderator'
    )
    AND u.last_login >= '2024-06-01'

-- Subquery in WHERE
WHERE u.user_id IN (
    SELECT o.user_id
    FROM orders AS o
    WHERE o.total_amount > 1000
        AND o.order_date >= '2024-01-01'
    GROUP BY o.user_id
    HAVING COUNT(*) >= 3
)

7. CTEs (WITH): Formatting Common Table Expressions

CTEs are one of the best tools for query readability. Formatting them well amplifies their benefit.

• WITH starts at the top level.
• Each CTE name and AS is on the same line.
• The CTE body is indented inside parentheses.
• Separate multiple CTEs with a blank line for visual grouping.
• The final SELECT starts at the same level as WITH.

WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(total_amount) AS revenue
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY DATE_TRUNC('month', order_date)
),

revenue_growth AS (
    SELECT
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
        ROUND(
            (revenue - LAG(revenue) OVER (ORDER BY month))
            / LAG(revenue) OVER (ORDER BY month) * 100,
            2
        ) AS growth_pct
    FROM monthly_revenue
)

SELECT
    month,
    revenue,
    prev_revenue,
    growth_pct
FROM revenue_growth
ORDER BY month;

8. Naming Conventions

Consistent naming eliminates guesswork and reduces errors.

• Tables -- Use snake_case, plural nouns: users, order_items, product_categories.
• Columns -- Use snake_case, descriptive names: created_at, is_active, total_amount.
• Aliases -- Use meaningful short aliases, not single letters: ord instead of o, usr instead of u. Exception: simple two-table JOINs where single letters are obvious.
• Avoid prefixes -- Do not prefix columns with table abbreviations (tbl_users, col_name). The table context is already clear from the query.
• Booleans -- Prefix with is_, has_, or can_: is_active, has_subscription, can_edit.
• Primary Keys -- Use id for the primary key. Foreign keys should be table_singular_id: user_id, order_id.

-- Good naming example
SELECT
    usr.user_id,
    usr.first_name,
    usr.is_active,
    ord.order_id,
    ord.total_amount,
    ord.created_at AS order_date
FROM users AS usr
INNER JOIN orders AS ord
    ON usr.user_id = ord.user_id
WHERE usr.is_active = TRUE
    AND usr.has_subscription = TRUE;

9. Bad vs Good: 5 Before/After Comparisons

Example 1: Simple SELECT

Bad:

select id,name,email,created_at from users where is_active=1 and role='admin' order by created_at desc limit 50;

Good:

SELECT
    id,
    name,
    email,
    created_at
FROM users
WHERE is_active = 1
    AND role = 'admin'
ORDER BY created_at DESC
LIMIT 50;

Example 2: Multi-Table JOIN

Bad:

select o.id,c.name,p.title,oi.qty,oi.price from orders o join customers c on o.customer_id=c.id join order_items oi on o.id=oi.order_id join products p on oi.product_id=p.id where o.status='completed' and o.created_at>='2024-01-01';

Good:

SELECT
    o.id AS order_id,
    c.name AS customer_name,
    p.title AS product_title,
    oi.qty,
    oi.price
FROM orders AS o
INNER JOIN customers AS c
    ON o.customer_id = c.id
INNER JOIN order_items AS oi
    ON o.id = oi.order_id
INNER JOIN products AS p
    ON oi.product_id = p.id
WHERE o.status = 'completed'
    AND o.created_at >= '2024-01-01';

Example 3: Complex WHERE

Bad:

select * from users where (role='admin' or role='moderator') and is_active=1 and (last_login>='2024-01-01' or created_at>='2024-06-01') and email like '%@company.com' order by last_login desc;

Good:

SELECT
    user_id,
    email,
    role,
    last_login,
    created_at
FROM users
WHERE (
        role = 'admin'
        OR role = 'moderator'
    )
    AND is_active = 1
    AND (
        last_login >= '2024-01-01'
        OR created_at >= '2024-06-01'
    )
    AND email LIKE '%@company.com'
ORDER BY last_login DESC;

Example 4: Subquery

Bad:

select u.name,u.email from users u where u.id in (select o.user_id from orders o where o.total>500 group by o.user_id having count(*)>3) and u.is_active=1;

Good:

SELECT
    u.name,
    u.email
FROM users AS u
WHERE u.id IN (
    SELECT o.user_id
    FROM orders AS o
    WHERE o.total > 500
    GROUP BY o.user_id
    HAVING COUNT(*) > 3
)
    AND u.is_active = 1;

Example 5: CTE with Aggregation

Bad:

with user_totals as (select user_id,sum(amount) as total from payments where status='completed' group by user_id), user_ranks as (select user_id,total,rank() over (order by total desc) as rnk from user_totals) select u.name,ur.total,ur.rnk from user_ranks ur join users u on ur.user_id=u.id where ur.rnk<=10;

Good:

WITH user_totals AS (
    SELECT
        user_id,
        SUM(amount) AS total
    FROM payments
    WHERE status = 'completed'
    GROUP BY user_id
),

user_ranks AS (
    SELECT
        user_id,
        total,
        RANK() OVER (ORDER BY total DESC) AS rnk
    FROM user_totals
)

SELECT
    u.name,
    ur.total,
    ur.rnk
FROM user_ranks AS ur
INNER JOIN users AS u
    ON ur.user_id = u.id
WHERE ur.rnk <= 10;

10. SQL Linters & Formatters

Automate formatting to eliminate style debates and ensure consistency.

Python-based linter/formatter. The most comprehensive option with 60+ configurable rules. Supports multiple SQL dialects (PostgreSQL, MySQL, BigQuery, Snowflake).

sqlfluff usage:

# Install
pip install sqlfluff

# Lint a file
sqlfluff lint query.sql --dialect postgres

# Auto-fix formatting
sqlfluff fix query.sql --dialect postgres

# Configuration (.sqlfluff in project root)
[sqlfluff]
dialect = postgres
templater = raw
max_line_length = 120

[sqlfluff:rules:capitalisation.keywords]
capitalisation_policy = upper

[sqlfluff:rules:capitalisation.identifiers]
capitalisation_policy = lower

[sqlfluff:indentation]
indent_unit = space
tab_space_size = 4

Perl-based formatter focused on PostgreSQL. Fast, simple, and produces clean output. Available as a CLI tool and web interface.

pgFormatter usage:

# Install (macOS)
brew install pgformatter

# Format a file
pg_format -u 1 -s 4 query.sql -o formatted.sql

# Options:
#   -u 1    : uppercase keywords
#   -s 4    : 4-space indent
#   -f 1    : function-style formatting
#   -W 5    : wrap after 5 columns in SELECT

JavaScript/npm package. Easy to integrate into Node.js projects, CI pipelines, and web applications. Supports multiple dialects.

sql-formatter (npm) usage:

// Install
npm install sql-formatter

// Usage in JavaScript/TypeScript
import { format } from 'sql-formatter';

const formatted = format(
  'select id,name from users where active=1',
  {
    language: 'postgresql',
    keywordCase: 'upper',
    tabWidth: 4,
    linesBetweenQueries: 2,
  }
);

console.log(formatted);
// SELECT
//     id,
//     name
// FROM
//     users
// WHERE
//     active = 1

11. IDE Settings

Configure your editor to format SQL automatically.

VS Code

Install the "SQL Formatter" extension (adpyke.vscode-sql-formatter). Add to settings.json:

// .vscode/settings.json
{
  "sql-formatter.uppercase": true,
  "sql-formatter.indent": "    ",
  "sql-formatter.linesBetweenQueries": 2,
  "[sql]": {
    "editor.defaultFormatter": "adpyke.vscode-sql-formatter",
    "editor.formatOnSave": true,
    "editor.tabSize": 4,
    "editor.insertSpaces": true
  }
}

DataGrip / JetBrains

DataGrip has built-in SQL formatting. Go to Settings > Editor > Code Style > SQL and configure. Key settings:

Settings > Editor > Code Style > SQL:
  - Word Case: Keywords = UPPER
  - Word Case: Identifiers = lower
  - Tabs and Indents:
    - Use tab character: OFF
    - Tab size: 4
    - Indent size: 4
  - Wrapping:
    - Wrap long lines: ON
    - Place commas: After item (trailing)
  - Clauses:
    - Place SELECT on new line: ON
    - Place FROM on new line: ON
    - Place ON in JOIN on new line: ON

DBeaver

DBeaver uses its own formatter. Go to Window > Preferences > SQL Editor > SQL Formatting. Enable "Insert spaces for tabs" and set keyword case to UPPER.

Window > Preferences > SQL Editor > SQL Formatting:
  - Keyword case: UPPER
  - Indent type: Spaces
  - Indent size: 4
  - SELECT: Column list on new line
  - JOIN: ON clause on new line

Shortcut: Ctrl+Shift+F (format selected SQL)

12. Team Style Guide Template

Copy and adapt this template for your team. Store it in your repository as SQL_STYLE_GUIDE.md.

# SQL Style Guide

## Keywords
- UPPERCASE for all SQL keywords (SELECT, FROM, WHERE, JOIN, etc.)

## Indentation
- 4 spaces (no tabs)
- One clause per line (SELECT, FROM, WHERE, GROUP BY, ORDER BY)
- Sub-clauses indented one level (JOIN ON, AND, OR)

## SELECT
- One column per line
- Trailing commas
- Always alias computed columns

## FROM / JOIN
- Explicit JOIN syntax (never comma joins)
- Always use AS for table aliases
- ON clause indented under its JOIN
- Multi-condition ON: each AND on its own line

## WHERE
- AND/OR at the beginning of lines (not the end)
- Explicit parentheses for mixed AND/OR
- Subqueries indented as nested blocks

## CTEs
- Preferred over subqueries for readability
- Blank line between CTEs
- Final SELECT at the same level as WITH

## Naming
- Tables: snake_case, plural (users, order_items)
- Columns: snake_case, descriptive (created_at, is_active)
- Aliases: meaningful abbreviations (ord, usr, prod)
- Booleans: is_, has_, can_ prefix
- Primary keys: id
- Foreign keys: {table_singular}_id

## Enforcement
- Pre-commit hook: sqlfluff lint
- CI check: sqlfluff lint --dialect postgres
- Config: .sqlfluff in repo root

FAQ