SQL formatting best practices: write queries anyone can read

Quick answer: Put each major clause (SELECT, FROM, JOIN, WHERE, GROUP BY, ORDER BY) on its own line, list one column per line, write explicit JOIN … ON conditions, name aliases meaningfully, replace nested subqueries with named CTEs, comment the business rules, and let a formatter such as SQLFluff enforce the style automatically.

SQL is read far more often than it is written — in code reviews, dashboards, incident investigations, and by the analyst who inherits your query next year. Unlike most languages, it has no single dominant style guide, so many teams end up with queries crammed onto one line or formatted differently in every file.

A handful of consistent conventions makes a dramatic difference. Here they are, with the reasoning behind each.

Put one clause per line

Start each major clause on its own line, so the structure of the query is visible from its left edge.

-- Hard to read
select u.id, u.email, count(o.id) as orders from users u left join orders o on o.user_id = u.id where u.created_at >= '2026-01-01' group by u.id, u.email having count(o.id) > 5 order by orders desc;

-- Easy to read
SELECT
  u.id,
  u.email,
  COUNT(o.id) AS order_count
FROM users AS u
LEFT JOIN orders AS o
  ON o.user_id = u.id
WHERE u.created_at >= '2026-01-01'
GROUP BY
  u.id,
  u.email
HAVING COUNT(o.id) > 5
ORDER BY order_count DESC;

The second version is longer, but a reader can find the filter, the grouping and the join condition instantly. It also produces cleaner diffs: changing the filter touches exactly one line.

Choose a keyword case and stick to it

Uppercase keywords (SELECT, FROM, WHERE) are the traditional convention. They visually separate the language from your table and column names, which helps in plain text without highlighting. Lowercase keywords are popular in teams that always read SQL in an editor.

Either is fine. Mixing both in one codebase is not. The same goes for identifiers: pick snake_case for tables and columns and use it everywhere.

Use leading or trailing commas — consistently

Two styles are common:

SELECT
  id,
  email,
  created_at

SELECT
    id
  , email
  , created_at

Leading commas make it easy to comment out or add the last column without touching the line above. Trailing commas read more naturally to most people. Pick one for the codebase and let your formatter enforce it.

Name things so readers don't have to decode them

• Use meaningful aliases. users AS u is fine in a short query. In a long one, customers AS c beats t1, t2, t3.
• Write AS explicitly for tables and columns, so an alias is never mistaken for a typo or a missing comma.
• Qualify every column with its table alias when a query involves more than one table. It removes ambiguity and protects the query if a column with the same name is added to another table later.
• Name computed columns (COUNT(o.id) AS order_count) so results and downstream code are self-describing.
• Put units in names when the type does not say: total_cents, duration_ms, size_bytes.

List columns instead of SELECT *

SELECT * is convenient while exploring, but fragile in shared code:

• Adding a column to the table silently changes the query's output.
• Code that reads columns by position can break.
• It may fetch large or sensitive columns you did not need.
• It prevents some index-only scans that could make the query faster.

Name the columns you use. In views and production queries, this is close to a rule.

Write explicit joins

Prefer JOIN … ON over listing tables separated by commas with the join conditions in the WHERE clause:

-- Implicit join: the join condition is mixed into the filters
SELECT o.id, c.email
FROM orders o, customers c
WHERE o.customer_id = c.id
  AND o.status = 'paid';

-- Explicit join: relationships and filters are separate
SELECT
  o.id,
  c.email
FROM orders AS o
INNER JOIN customers AS c
  ON c.id = o.customer_id
WHERE o.status = 'paid';

Explicit joins keep each relationship next to the table it belongs to, make the join type (INNER, LEFT) obvious, and make an accidentally missing condition — which would produce a huge cross join — much easier to spot.

Replace nested subqueries with CTEs

Nested subqueries force readers to start at the innermost query and work outward. Common table expressions (WITH clauses) let you name each step and read from top to bottom:

WITH recent_orders AS (
  SELECT
    customer_id,
    total_cents
  FROM orders
  WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
),

spend_per_customer AS (
  SELECT
    customer_id,
    SUM(total_cents) AS spend_cents
  FROM recent_orders
  GROUP BY customer_id
)

SELECT
  c.email,
  s.spend_cents
FROM spend_per_customer AS s
INNER JOIN customers AS c
  ON c.id = s.customer_id
ORDER BY s.spend_cents DESC
LIMIT 10;

Each CTE name documents its purpose, and you can debug the query by selecting from one CTE at a time.

Does a CTE hurt performance? In most modern databases, no. PostgreSQL, for example, has inlined simple non-recursive CTEs into the main query since version 12 (you can force the old behavior with MATERIALIZED). If performance is critical, check the query plan with EXPLAIN rather than guessing. Date and interval syntax also varies between databases; the example above uses PostgreSQL.

Comment the business rules

The logic that matters most often hides in the WHERE clause. status NOT IN (4, 7) means nothing on its own; a comment turns it into documentation:

WHERE o.status NOT IN (4, 7)  -- exclude refunded (4) and test (7) orders
  AND o.total_cents > 0       -- zero-value orders are internal adjustments

Comment why a filter exists, not what the SQL syntax does.

Avoid the NOT IN with NULL trap

A formatting guide is also a good place to standardize safe patterns. One classic bug: NOT IN with a subquery returns no rows at all if the subquery returns a single NULL.

-- Returns nothing if any orders.customer_id is NULL
SELECT id FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- Safe and usually just as fast
SELECT c.id
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.id
);

Prefer NOT EXISTS for anti-joins.

Present schemas clearly

When you share a table design in a review or onboarding document, the CREATE TABLE statement is often clearer than a diagram for a handful of tables, because it shows types, nullability, defaults and constraints precisely:

CREATE TABLE orders (
  id           BIGSERIAL    PRIMARY KEY,
  customer_id  BIGINT       NOT NULL REFERENCES customers (id),
  status       TEXT         NOT NULL DEFAULT 'pending',
  total_cents  INTEGER      NOT NULL CHECK (total_cents >= 0),
  created_at   TIMESTAMPTZ  NOT NULL DEFAULT now()
);

CREATE INDEX orders_customer_id_idx ON orders (customer_id);

• Align names, types and constraints into columns so the eye can scan them.
• Include indexes and foreign keys — they are what reviewers most often need to discuss.
• Add a comment for non-obvious columns, or use the database's COMMENT ON COLUMN.
• Use a diagram as well for larger models, roughly ten tables or more.

Automate the style

Arguing about commas in code review is a waste of everyone's time. Agree on a configuration once and let tools enforce it:

• SQLFluff — a linter and formatter that supports many SQL dialects and dbt.
• sql-formatter — a JavaScript formatter with editor integrations.
• pgFormatter — a formatter focused on PostgreSQL.

Run the formatter in your editor on save and in CI, and the style guide maintains itself. For a human-readable reference, the SQL Style Guide by Simon Holywell is a widely cited starting point.

Sharing queries in docs, posts and reviews

Keep queries as text in documentation and pull requests, so people can run them. When a query or schema is the subject of a post, a tutorial or a slide, the SQL query image tool and the database schema image generator highlight it while preserving your formatting — so the conventions above carry straight into the image.

Frequently asked questions

Should SQL keywords be uppercase?

It is a convention, not a requirement — SQL keywords are case-insensitive. Uppercase keywords help readability in plain text; lowercase is common in teams that rely on syntax highlighting. Consistency matters more than the choice.

Are CTEs slower than subqueries?

Usually not in modern databases. PostgreSQL 12 and later inline simple CTEs, and most other engines optimize them similarly. Check the execution plan with EXPLAIN if performance is critical.

Why is SELECT * bad practice?

It makes query output change whenever the table changes, can break code that relies on column order, fetches columns you do not need, and can prevent some index-only optimizations. List columns explicitly in shared code.

What is the best SQL formatter?

SQLFluff is a popular choice because it both lints and formats many dialects and is configurable. sql-formatter is a good lightweight option for JavaScript-based tooling and editors.