SQL Formatting: Why It Matters and How to Do It Right

SQL is the lingua franca of data, but poorly formatted SQL can be a nightmare to read, debug, and maintain. Beyond aesthetics, formatting directly impacts performance — sloppy patterns like SELECT *, hidden type conversions, or deep LIMIT offsets can silently degrade your database. This guide covers the why and how of SQL formatting, focusing on readability, performance, and maintainability, with a deep dive into common anti-patterns.

Try it in our SQL formatter to instantly clean up your queries.

Why Formatting Matters

• Readability: Consistent indentation and line breaks make complex queries understandable at a glance. Your future self (and your teammates) will thank you.
• Debugging: Well-structured SQL makes it easier to spot missing joins, incorrect filters, or misplaced parentheses.
• Performance Awareness: Formatting encourages you to think about what the database actually does. For example, writing SELECT * instead of explicit columns often signals a lack of consideration for I/O and indexing.
• Code Review: Clean SQL is easier to review. Reviewers can focus on logic, not deciphering a wall of text.

Key Formatting Principles

1. Use Explicit Column Lists

Always specify the columns you need. SELECT * is a notorious anti-pattern:

-- Bad: reads all columns, harder to index-cover, fragile to schema changes
SELECT * FROM orders WHERE user_id = 1001;

-- Good: only fetches what's needed, can leverage covering indexes
SELECT order_id, order_no, total_amount, created_at
FROM orders
WHERE user_id = 1001;

2. Capitalize SQL Keywords

Use uppercase for SQL keywords (SELECT, FROM, WHERE, JOIN, etc.) and lowercase for table/column names. This convention improves scanability:

SELECT o.order_id, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'PAID';

3. Align Clauses Vertically

Start each major clause on a new line and align sub-clauses with indentation:

SELECT
    o.order_id,
    o.order_no,
    o.total_amount
FROM
    orders o
WHERE
    o.created_at >= '2026-01-01'
    AND o.status IN ('PAID', 'SHIPPED')
ORDER BY
    o.created_at DESC
LIMIT 20;

4. Format JOINs Clearly

Place JOIN conditions on the same line as the ON keyword, and indent the ON clause:

SELECT
    u.id,
    u.name,
    o.order_id
FROM
    users u
    JOIN orders o ON o.user_id = u.id
WHERE
    u.created_at >= '2025-01-01';

Common Anti-Patterns That Hurt Performance

1. Functions on Indexed Columns in WHERE

Wrapping a column in a function (e.g., DATE(created_at)) often disables index usage:

-- Bad: index on created_at cannot be used
SELECT * FROM orders WHERE DATE(created_at) = '2026-03-26';

-- Good: range query uses index
SELECT order_id, created_at
FROM orders
WHERE created_at >= '2026-03-26 00:00:00'
  AND created_at < '2026-03-27 00:00:00';

Rule of thumb: Transform the constant, not the column.

2. Deep OFFSET Pagination

Using LIMIT offset, size with a large offset forces the database to scan and discard many rows:

-- Bad: scans 100,020 rows, discards 100,000
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- Good: keyset pagination uses index to skip directly
SELECT order_id, id
FROM orders
WHERE id > 100000
ORDER BY id
LIMIT 20;

3. NOT IN with Subqueries (NULL Trap)

NOT IN returns zero rows if the subquery result contains any NULL:

-- Bad: if orders.user_id has a NULL, this returns no rows
SELECT * FROM customer WHERE id NOT IN (SELECT customer_id FROM orders);

-- Good: NOT EXISTS is safe
SELECT * FROM customer c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

4. OR Conditions That Break Index Usage

OR can prevent the optimizer from using a composite index efficiently:

-- Bad: may not use indexes optimally
SELECT * FROM orders WHERE status = 'PAID' OR user_id = 1001;

-- Better: split into UNION ALL
SELECT * FROM orders WHERE status = 'PAID'
UNION ALL
SELECT * FROM orders WHERE user_id = 1001 AND status <> 'PAID';

5. UPDATE/DELETE Without WHERE or LIMIT

Always confirm the affected rows before executing destructive statements:

-- Risky: updates all rows
UPDATE users SET status = 'inactive';

-- Safer: preview first
SELECT COUNT(*) FROM users WHERE last_login_at < '2025-01-01';
-- Then execute with LIMIT if supported
UPDATE users SET status = 'inactive'
WHERE last_login_at < '2025-01-01'
LIMIT 1000;

Worked Example: Refactoring a Messy Query

Let's take a poorly written query and refactor it step by step.

Original (poor readability, anti-patterns):

SELECT * FROM operation WHERE type='SQLStats' AND name='SlowLog' ORDER BY create_time LIMIT 1000,10;

Step 1: Format for readability

SELECT *
FROM operation
WHERE type = 'SQLStats'
  AND name = 'SlowLog'
ORDER BY create_time
LIMIT 1000, 10;

Step 2: Replace SELECT * with explicit columns

SELECT id, type, name, create_time, status
FROM operation
WHERE type = 'SQLStats'
  AND name = 'SlowLog'
ORDER BY create_time
LIMIT 1000, 10;

Step 3: Fix deep pagination with keyset pagination

Assume we have a composite index on (type, name, create_time).

SELECT id, type, name, create_time, status
FROM operation
WHERE type = 'SQLStats'
  AND name = 'SlowLog'
  AND create_time > '2017-03-16 14:00:00'  -- last page's max create_time
ORDER BY create_time
LIMIT 10;

Step 4: Verify with EXPLAIN

EXPLAIN
SELECT id, type, name, create_time, status
FROM operation
WHERE type = 'SQLStats'
  AND name = 'SlowLog'
  AND create_time > '2017-03-16 14:00:00'
ORDER BY create_time
LIMIT 10;

Check that type is range or ref, key shows the index, and rows is small.

Comparison: IN vs EXISTS vs JOIN

Common Pitfalls

• Using SELECT * in production code – increases I/O and breaks on schema changes.
• Applying functions to indexed columns in WHERE – disables index usage.
• Writing NOT IN (SELECT ...) without checking for NULL – silent logical errors.
• Using deep LIMIT offset for pagination – performance degrades with page depth.
• Adding indexes without analyzing query patterns – creates redundant indexes that slow writes.
• Optimizing by guesswork instead of reading EXPLAIN plans.

FAQ

Does SQL formatting actually affect performance?

No – formatting itself doesn't change the execution plan. But the habits that come with good formatting (explicit columns, no functions on columns, proper pagination) directly improve performance. Formatting makes those habits easier to spot.

Should I always use NOT EXISTS instead of NOT IN?

For subqueries, yes – NOT EXISTS avoids the NULL trap and often performs better. For static value lists, NOT IN is fine (e.g., WHERE status NOT IN ('CANCELLED', 'REFUNDED')).

How do I paginate efficiently in SQL?

Use keyset pagination (also called seek method): filter by the last seen row's unique column(s) instead of OFFSET. For example: WHERE id > 1000 ORDER BY id LIMIT 20. This works well for sorted, unique columns.

What's the best way to format a long SQL query?

Use a consistent style: capitalize keywords, start each clause on a new line, indent sub-clauses, and align ON conditions. Many teams adopt a style guide (e.g., SQL Style Guide). Our SQL formatter can help automate this.

How do I check if my query is using indexes?

Run EXPLAIN before your query. Look at the type column (aim for const, eq_ref, ref, range – avoid ALL), key (should show an index name), and rows (should be small relative to table size).