The Practical Guide to SQL Formatting and Query Optimization: Writing Queries That Actually Run Fast

We have all had to open a legacy codebase, look at a 400-line database query, and quietly whisper to ourselves, “Who wrote this monstrosity?”

Then you run git blame, and you realize you wrote it six months ago.

SQL (Structured Query Language) has been powering relational databases since the 1970s. From PostgreSQL and MySQL to cloud data warehouses like Snowflake, BigQuery, and DuckDB, SQL is how modern software asks questions of data.

Yet because SQL is so flexible, queries easily turn into spaghetti code: unindented subqueries nested five levels deep, lowercase keywords, missing table aliases, and SELECT * statements pulling gigabytes of unused data across the network.

In this practical, in-depth guide, we will cover the core rules of clean SQL formatting, break down common performance traps, explain Common Table Expressions (CTEs), analyze query execution mechanics, and show you how to write queries that are easy to read and lightning fast to execute.

                    +------------------------------------+
                    |        THE ANATOMY OF CLEAN SQL    |
                    +-----------------+------------------+
                                      |
            +-------------------------+-------------------------+
            |                                                   |
            v                                                   v
+-------------------------+                         +-------------------------+
|   SPAGHETTI QUERY       |                         |   STRUCTURED SQL QUERY  |
|   select u.id,count(*)  |                         |   SELECT                |
|   from users u join...  |                         |     u.id, COUNT(*)      |
|   (One giant unreadable |                         |   FROM users u          |
|    single line blob)    |                         |   LEFT JOIN orders o... |
+------------+------------+                         +------------+------------+
             |                                                   |
             v                                                   v
+-------------------------+                         +-------------------------+
|   Hard to debug         |                         |   Clear visual hierarchy|
|   Hidden join bugs      |                         |   Fast code reviews     |
+-------------------------+                         +-------------------------+

The Five Golden Rules of Clean SQL

Clean SQL is not just about looking pretty. It is about making bugs impossible to hide.

Rule 1: Always Capitalize SQL Keywords

Write SELECT, FROM, WHERE, LEFT JOIN, ON, GROUP BY, HAVING, and ORDER BY in UPPERCASE. Keep your table and column names in lowercase snake_case (user_id, created_at). This immediately tells your brain what is a command and what is data.

Rule 2: Put One Column Per Line

Never put twenty selected columns on a single horizontal line. Put each column on its own indented line with a trailing comma. When someone adds or removes a column later, the Git diff shows a clean one-line change instead of modifying the entire paragraph.

Rule 3: Always Use Explicit Table Aliases

In multi-table queries, always prefix columns with clear aliases (u.email, o.total_amount). Never leave the database guessing which table a column belongs to.

Rule 4: Indent JOIN and ON Conditions Clearly

Place each JOIN on a new line, and put the matching ON condition indented directly beneath it.

Rule 5: Replace Deeply Nested Subqueries with CTEs

Instead of nesting subqueries inside subqueries inside subqueries, use Common Table Expressions (WITH blocks) to break your query into logical steps.

+-------------------+-------------------------------+-----------------------------------+
| Rule              | Bad Practice                  | Clean Practice                    |
+-------------------+-------------------------------+-----------------------------------+
| Keyword Casing    | select id from users          | SELECT id FROM users              |
| Column Layout     | SELECT id, email, name, city  | SELECT\n  id,\n  email,\n  name   |
| Table Aliases     | SELECT email FROM users, orders| SELECT u.email FROM users u...    |
| Joins             | JOIN orders ON u.id = o.user_id| LEFT JOIN orders o\n  ON u.id =...|
| Complex Logic     | 5-level nested subqueries     | WITH active_users AS (...)        |
+-------------------+-------------------------------+-----------------------------------+

Before and After: The Power of Formatting

Look at this typical unformatted query:

select u.id,u.email,count(o.id) as order_count,sum(o.total) as lifetime_value from users u left join orders o on u.id=o.user_id where u.status='active' and u.created_at>='2026-01-01' group by u.id,u.email having count(o.id)>3 order by lifetime_value desc limit 50;

Now look at the exact same query after running it through our Free Online SQL Formatter:

SELECT
    u.id,
    u.email,
    COUNT(o.id) AS order_count,
    SUM(o.total) AS lifetime_value
FROM users u
LEFT JOIN orders o
    ON u.id = o.user_id
WHERE
    u.status = 'active'
    AND u.created_at >= '2026-01-01'
GROUP BY
    u.id,
    u.email
HAVING
    COUNT(o.id) > 3
ORDER BY
    lifetime_value DESC
LIMIT 50;

Notice how much easier that is to scan and verify in five seconds.

Modularizing Complex Queries with CTEs (WITH Clause)

Deeply nested subqueries are notoriously difficult to read and debug. When a query is nested inside a FROM clause which is nested inside a WHERE IN subquery, nobody on your team can understand the data flow.

Common Table Expressions (CTEs) solve this by letting you define named temporary result sets that read like a top-to-bottom recipe:

WITH active_subscribers AS (
    SELECT
        id AS user_id,
        email,
        created_at
    FROM users
    WHERE
        status = 'active'
        AND is_email_verified = TRUE
),
order_totals AS (
    SELECT
        user_id,
        COUNT(id) AS total_orders,
        SUM(order_value) AS lifetime_spend
    FROM orders
    WHERE status = 'completed'
    GROUP BY user_id
)
SELECT
    s.user_id,
    s.email,
    COALESCE(o.total_orders, 0) AS total_orders,
    COALESCE(o.lifetime_spend, 0) AS lifetime_spend
FROM active_subscribers s
LEFT JOIN order_totals o
    ON s.user_id = o.user_id
WHERE COALESCE(o.lifetime_spend, 0) > 100
ORDER BY lifetime_spend DESC;

Four Common SQL Performance Anti-Patterns to Avoid

1. The SELECT * Trap in Production

Writing SELECT * in production web queries forces your database to read every single column from disk and transmit it across the network. If your table has a jsonb_metadata or bio_text column with thousands of characters, your query slows down by 10x. Only select the exact columns your application needs.

2. The Function-on-Indexed-Column Trap

If you have an index on created_at and you write:

-- SLOW: Invalidates index!
WHERE YEAR(created_at) = 2026

The database has to execute the YEAR() function for every single row in the table, performing a slow full table scan.

Instead, write:

-- FAST: Uses B-Tree index range scan!
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'

3. The Leading Wildcard LIKE Trap

Writing WHERE name LIKE '%smith' forces a full table scan because the B-Tree index cannot know which letter to look up first. If you need substring searches across massive text, use PostgreSQL Trigram indexes (pg_trgm) or full-text search.

4. The Accidental Cartesian Product (Missing Join Conditions)

If you join two tables without specifying the matching foreign key in the ON clause, the database pairs every row in Table A with every row in Table B. If Table A has 10,000 rows and Table B has 10,000 rows, your query creates 100 million intermediate rows, exhausting database RAM.

If you ever need to compare the output of two SQL queries side by side to ensure your refactored query returns the exact same data, use our Text Diff Tool.

Conclusion: Format Your Queries Instantly

Writing clean SQL makes you a better developer, simplifies pull requests, and saves your team hours of debugging pain.

Whenever you need to tidy up a messy query, format it in a flash with the TextSorter SQL Formatter. It runs 100% locally in your browser, keeping your database structure completely private.

Deep Dive: How Database Query Planners Actually Execute SQL

When you hit execute on a SQL query, the database management system (whether it is PostgreSQL, MySQL, Oracle, or SQL Server) does not simply start scanning tables at random.

Behind the scenes, the database engine passes your query through a sophisticated four-stage optimization pipeline:

+---------------------+      +---------------------+      +---------------------+      +---------------------+
|   1. SQL PARSER     | ---> |   2. QUERY REWRITER | ---> |   3. QUERY PLANNER  | ---> |   4. EXEC ENGINE    |
|   Checks grammar    |      |   Simplifies views  |      |   Calculates costs  |      |   Reads disk blocks |
|   Builds AST tree   |      |   Applies security  |      |   Chooses indexes   |      |   Returns rows      |
+---------------------+      +---------------------+      +---------------------+      +---------------------+

Stage 1: The Parser and Lexer

The parser breaks your SQL text into tokens, verifies syntax rules, and constructs an Abstract Syntax Tree (AST). If you misspelled SELEKT or forgot a closing parenthesis, the parser halts execution and returns a syntax error.

Stage 2: The Query Rewriter

The rewriter transforms your AST into an optimized logical tree. It expands database views into their underlying table references, eliminates redundant conditions (like WHERE 1=1), and rewrites subqueries into joins where possible.

Stage 3: The Cost-Based Query Planner (The Optimizer)

This is the mathematical brain of the database. The query planner inspects table statistics (stored in system catalogs like pg_statistic in PostgreSQL), analyzes data distribution histograms, and evaluates thousands of possible execution strategies. It calculates an estimated “cost” in disk I/O and CPU cycles for each strategy and chooses the cheapest path.

Stage 4: The Execution Engine

The execution engine runs the chosen physical plan, reading data pages from memory buffers (or disk), applying filters, performing index lookups, executing hash joins, and streaming results back to the client.

Understanding EXPLAIN ANALYZE Output Like a Pro

To optimize slow database queries, you must learn to read the output of EXPLAIN ANALYZE.

Look at this real-world PostgreSQL execution plan:

Nested Loop  (cost=0.56..45.89 rows=10 width=72) (actual time=0.034..0.125 rows=8 loops=1)
  ->  Index Scan using idx_users_status on users u  (cost=0.28..12.45 rows=12 width=36) (actual time=0.018..0.025 rows=8 loops=1)
        Index Cond: (status = 'active'::text)
        Filter: (created_at >= '2026-01-01'::date)
  ->  Index Scan using idx_orders_user_id on orders o  (cost=0.28..2.77 rows=1 width=36) (actual time=0.008..0.010 rows=1 loops=8)
        Index Cond: (user_id = u.id)
Planning Time: 0.142 ms
Execution Time: 0.165 ms

Key Plan Metrics Decoded:

• Index Scan vs Seq Scan: An Index Scan uses a B-Tree index to jump directly to matching rows in O(log N) time. A Sequential Scan (Seq Scan) reads every single block of the table from disk in O(N) time.
• Cost (0.56..45.89): The first number is startup cost (time before the first row is emitted); the second number is total estimated cost.
• Actual Time (0.034..0.125): The real wall-clock time in milliseconds measured during query execution.
• Loops: The number of times that specific node was executed. In nested loops, inner index scans execute once per outer row.

Advanced Indexing Strategies for Blazing Performance

Having indexes is great, but having the RIGHT indexes is what makes queries run 100x faster:

1. Composite (Multi-Column) Indexes

If your query frequently filters on two columns together:

SELECT id, email FROM users WHERE organization_id = 42 AND status = 'active';

Creating a composite index on (organization_id, status) allows the database to locate matching rows in a single index lookup.

The Leftmost Prefix Rule: A composite index on (A, B, C) can speed up searches on:

• WHERE A = ...
• WHERE A = ... AND B = ...
• WHERE A = ... AND B = ... AND C = ... However, it CANNOT be used for searches filtering only on WHERE B = ... or WHERE C = ....

2. Partial (Filtered) Indexes

If you have a table with 10 million users, but only 5% of them have is_suspended = true, creating a standard index on is_suspended wastes hundreds of megabytes of RAM.

A Partial Index indexes only the rows that match a specific predicate:

CREATE INDEX idx_suspended_users ON users (id) WHERE is_suspended = TRUE;

This index is 95% smaller, fits entirely in CPU L3 cache, and speeds up admin suspension queries dramatically.

3. Covering Indexes (Index-Only Scans with INCLUDE)

In PostgreSQL and SQL Server, you can include non-search payload columns directly in the leaf pages of an index:

CREATE INDEX idx_users_lookup ON users (email) INCLUDE (first_name, last_name);

When you run SELECT first_name, last_name FROM users WHERE email = 'alex@example.com', the database never touches the main table heap on disk. It reads everything directly from the index (an Index-Only Scan), reducing disk I/O to zero.

Master Checklist for Writing High-Performance SQL

1. Format with Clear Hierarchy: Use uppercase keywords and indent joins so that code reviews spot logic errors immediately. Format queries in seconds with the TextSorter SQL Formatter.
2. Eliminate SELECT *: Only request the exact columns required by the application.
3. Avoid Functions on Indexed Columns: Rewrite WHERE DATE(created_at) = ... into range boundaries.
4. Use UNION ALL Instead of UNION: Avoid unnecessary sorting and deduplication steps when data sets are already disjoint.
5. Add Meaningful Indexes: Create composite and partial indexes based on actual production query patterns.
6. Keep Transactions Short: Avoid holding long database transactions that lock rows and block concurrent writers.