SQL Formatter: Best Practices for Readable Queries

Well-formatted SQL is easier to read, debug, and maintain. This guide covers SQL formatting conventions and best practices.

Basic Formatting Rules

1. Keyword Capitalization

-- Recommended: UPPERCASE keywords
SELECT id, name, email
FROM users
WHERE status = 'active'
ORDER BY created_at DESC;

-- Alternative: lowercase keywords
select id, name, email
from users
where status = 'active'
order by created_at desc;

-- Avoid: Mixed case
Select Id, Name, Email
From Users
Where Status = 'active';

2. Indentation

-- Good: Consistent indentation
SELECT
    u.id,
    u.name,
    u.email,
    COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.status = 'active'
    AND u.created_at > '2024-01-01'
GROUP BY u.id, u.name, u.email
HAVING COUNT(o.id) > 5
ORDER BY order_count DESC
LIMIT 10;

3. Line Breaks

-- Bad: Everything on one line
SELECT u.id, u.name, u.email, COUNT(o.id) FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE u.status = 'active' GROUP BY u.id ORDER BY u.name;

-- Good: Logical line breaks
SELECT
    u.id,
    u.name,
    u.email,
    COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o
    ON o.user_id = u.id
WHERE u.status = 'active'
GROUP BY u.id, u.name, u.email
ORDER BY u.name;

Complex Query Formatting

Subqueries

SELECT
    u.id,
    u.name,
    (
        SELECT COUNT(*)
        FROM orders o
        WHERE o.user_id = u.id
    ) AS order_count,
    (
        SELECT MAX(o.created_at)
        FROM orders o
        WHERE o.user_id = u.id
    ) AS last_order
FROM users u;

Common Table Expressions (CTEs)

WITH active_users AS (
    SELECT id, name, email
    FROM users
    WHERE status = 'active'
),
user_orders AS (
    SELECT
        user_id,
        COUNT(*) AS order_count,
        SUM(total) AS total_spent
    FROM orders
    GROUP BY user_id
)
SELECT
    au.id,
    au.name,
    au.email,
    COALESCE(uo.order_count, 0) AS order_count,
    COALESCE(uo.total_spent, 0) AS total_spent
FROM active_users au
LEFT JOIN user_orders uo
    ON uo.user_id = au.id
ORDER BY uo.total_spent DESC;

CASE Statements

SELECT
    id,
    name,
    CASE
        WHEN total >= 1000 THEN 'premium'
        WHEN total >= 500 THEN 'standard'
        WHEN total >= 100 THEN 'basic'
        ELSE 'new'
    END AS customer_tier,
    CASE status
        WHEN 'A' THEN 'Active'
        WHEN 'I' THEN 'Inactive'
        WHEN 'P' THEN 'Pending'
        ELSE 'Unknown'
    END AS status_label
FROM customers;

JOIN Formatting

-- Explicit JOIN conditions
SELECT
    o.id AS order_id,
    o.total,
    u.name AS customer_name,
    p.name AS product_name
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'
    AND o.created_at >= '2024-01-01';

WHERE Clause Formatting

-- Multiple conditions
SELECT *
FROM products
WHERE category_id = 5
    AND price BETWEEN 10 AND 100
    AND (
        status = 'active'
        OR featured = TRUE
    )
    AND name LIKE '%search%';

-- IN clause
SELECT *
FROM orders
WHERE status IN (
    'pending',
    'processing',
    'shipped'
);

INSERT Formatting

-- Single row
INSERT INTO users (
    name,
    email,
    status,
    created_at
) VALUES (
    'John Doe',
    'john@example.com',
    'active',
    NOW()
);

-- Multiple rows
INSERT INTO products (name, price, category_id) VALUES
    ('Product A', 19.99, 1),
    ('Product B', 29.99, 1),
    ('Product C', 39.99, 2);

UPDATE Formatting

UPDATE users
SET
    status = 'inactive',
    updated_at = NOW(),
    updated_by = 'system'
WHERE last_login < '2023-01-01'
    AND status = 'active';

JavaScript SQL Formatter

function formatSQL(sql) {
  const keywords = [
    'SELECT', 'FROM', 'WHERE', 'AND', 'OR',
    'JOIN', 'LEFT', 'RIGHT', 'INNER', 'OUTER',
    'ON', 'GROUP BY', 'ORDER BY', 'HAVING',
    'LIMIT', 'OFFSET', 'INSERT', 'UPDATE',
    'DELETE', 'SET', 'VALUES', 'INTO'
  ];

  let formatted = sql.toUpperCase();

  // Add line breaks before keywords
  keywords.forEach(kw => {
    formatted = formatted.replace(
      new RegExp(`\b${kw}\b`, 'g'),
      `
${kw}`
    );
  });

  // Clean up
  return formatted
    .replace(/^
/, '')
    .replace(/
+/g, '
')
    .trim();
}

Try Our Tools

• SQL Formatter - Format SQL queries
• SQL to JSON - Export SQL as JSON
• SQL to CSV - Export SQL as CSV

Conclusion

Consistent SQL formatting improves readability and maintainability. Choose a style guide and apply it consistently across your project.