devprix.io
All posts

How to format complex SQL queries

Format a messy one-line SQL query with CTEs, joins, CASE and a window function step by step, and see how comma style and layout shape code review diffs.

By Last reviewed 7 min readSQLFormattingCode review

The SQL you most need to read rarely arrives in a readable shape. It comes from a slow-query log, an ORM's debug output or a tracing span, flattened onto one line in whatever keyword case the author felt like. Before you can review it or work out why it's slow, you need to see its structure: which SELECT owns which WHERE, where each CTE ends, what each table is joined on.

This guide formats one realistic query a step at a time and explains the rule behind each change. Paste the same query in and format the SQL in your browser as you read. Every formatted block below is taken from the formatter's actual output with its default settings (Standard SQL, 2-space indent, uppercase keywords, trailing commas, Compact WHERE off) unless the text says otherwise.

The query

This is the kind of thing you copy out of a log. It has two CTEs, an inner and a left join, a CASE expression, a window function, an IN subquery and aggregates:

With recent_orders AS (select o.customer_id, o.total, o.created_at from orders o where o.created_at >= '2026-01-01' and o.status <> 'cancelled'), ranked as (SELECT r.customer_id, r.total, r.created_at, row_number() over (partition by r.customer_id order by r.total desc) as rn FROM recent_orders r) select c.id, c.name, sum(r.total) as total_spent, case when sum(r.total) > 10000 then 'gold' when sum(r.total) > 1000 then 'silver' else 'bronze' end as tier, max(case when r.rn = 1 then r.created_at end) as biggest_order_at from customers c join ranked r on r.customer_id = c.id left join regions g on g.id = c.region_id where c.id in (select customer_id from support_tickets where severity = 'high') and g.code is not null group by c.id, c.name order by total_spent desc;

On one line you can't answer basic questions. Does where severity = 'high' filter the outer query or the subquery? Where does ranked end? Five rules answer them.

Step 1: one keyword case

Uppercase keywords and lowercase identifiers split the text into two layers, the SQL language and your schema, so your eye can jump from SELECT to FROM to WHERE without reading the column names in between. The formatter uppercases every word it recognises as a keyword or built-in function (SUM, ROW_NUMBER) and leaves identifiers and string literals exactly as written.

Unticking Uppercase keywords keeps keywords in the case they were typed rather than lowercasing them, so it won't tidy a query that mixes With, select and SELECT. If your team prefers lowercase, write it that way and leave the option off.

The formatter decides what is a keyword from a word list, so a column named date or key gets uppercased too. Unquoted identifiers are case-insensitive in most databases, so the query means the same thing; SQL formatter vs SQL parser explains why a formatter can't tell the difference.

Step 2: one clause per line

Each clause keyword starts a line at the current nesting level, and what the clause contains is indented one step under it, one item per line. Joins sit at the clause level with their ON condition indented beneath, so each join reads as a unit: what is joined, then how.

FROM
  customers c
JOIN ranked r
  ON r.customer_id = c.id
LEFT JOIN regions g
  ON g.id = c.region_id

With Compact WHERE off, every AND and OR in a WHERE clause (outside parentheses) starts its own line. Keep it that way for SQL that will be reviewed: one condition per line means a diff points at the condition that changed. Compact WHERE keeps them on one line, fine for a quick read.

Step 3: indent CTEs and subqueries as queries of their own

A subquery is a complete query, so it should look like one. When the formatter meets an opening parenthesis followed by SELECT, it lays out the contents as a statement one level deeper and puts the closing parenthesis back at the indent of the line that opened it. Nesting depth becomes indentation depth:

WITH recent_orders AS (
  SELECT
    o.customer_id,
    o.total,
    o.created_at
  FROM
    orders o
  WHERE
    o.created_at >= '2026-01-01'
    AND o.status <> 'cancelled'
), ranked AS (

The IN subquery gets the same treatment, and now severity = 'high' visibly belongs to support_tickets, not to the outer query:

WHERE
  c.id IN (
    SELECT
      customer_id
    FROM
      support_tickets
    WHERE
      severity = 'high'
  )
  AND g.code IS NOT NULL

Step 4: CASE expressions and window functions

A CASE expression in the select list gets one line per branch, indented under CASE, with END and its alias back at the level where the CASE started:

  CASE
    WHEN SUM(r.total) > 10000 THEN 'gold'
    WHEN SUM(r.total) > 1000 THEN 'silver'
    ELSE 'bronze'
  END AS tier,
  MAX(CASE WHEN r.rn = 1 THEN r.created_at END) AS biggest_order_at

The last line shows the other half of the rule: anything inside parentheses that isn't a subquery (function calls, IN lists, window definitions) stays on one line. That keeps MAX(CASE … END) compact, and it's why the window function comes out as ROW_NUMBER() OVER (PARTITION BY r.customer_id ORDER BY r.total DESC) AS rn. For a short window that's the right call. Once a window has several partition or sort columns, break it by hand (this is recommended layout, not tool output):

ROW_NUMBER() OVER (
  PARTITION BY r.customer_id, r.region_id
  ORDER BY r.total DESC, r.created_at
) AS rn

Formatting again folds this back onto one line, so don't reformat a hand-tuned query wholesale.

Step 5: choose a comma style and keep it

Trailing commas, the default, read like prose. Untick Trailing commas and each item starts with its comma instead:

SELECT
  c.id
  , c.name
  , SUM(r.total) AS total_spent

The difference shows up in diffs. Say the query gains c.email, which must be added to the GROUP BY as well. Appending it with trailing commas touches two lines, because the old last item needs a comma:

 GROUP BY
   c.id,
-  c.name
+  c.name,
+  c.email

With leading commas the same change is a single added line, + , c.email. The trade flips at the top of a list, where inserting a new first item touches two lines. Leading commas also let you comment out the last item without leaving a dangling comma. Neither style is wrong; mixing them in one codebase is.

Format before code review and before diffing

Here is the whole query, formatted:

WITH recent_orders AS (
  SELECT
    o.customer_id,
    o.total,
    o.created_at
  FROM
    orders o
  WHERE
    o.created_at >= '2026-01-01'
    AND o.status <> 'cancelled'
), ranked AS (
  SELECT
    r.customer_id,
    r.total,
    r.created_at,
    ROW_NUMBER() OVER (PARTITION BY r.customer_id ORDER BY r.total DESC) AS rn
  FROM
    recent_orders r
)
SELECT
  c.id,
  c.name,
  SUM(r.total) AS total_spent,
  CASE
    WHEN SUM(r.total) > 10000 THEN 'gold'
    WHEN SUM(r.total) > 1000 THEN 'silver'
    ELSE 'bronze'
  END AS tier,
  MAX(CASE WHEN r.rn = 1 THEN r.created_at END) AS biggest_order_at
FROM
  customers c
JOIN ranked r
  ON r.customer_id = c.id
LEFT JOIN regions g
  ON g.id = c.region_id
WHERE
  c.id IN (
    SELECT
      customer_id
    FROM
      support_tickets
    WHERE
      severity = 'high'
  )
  AND g.code IS NOT NULL
GROUP BY
  c.id,
  c.name
ORDER BY
  total_spent DESC;

Laid out like this, a reviewer can spot what the one-liner hid: regions is LEFT JOINed, but WHERE requires g.code IS NOT NULL. A customer with no region gets a NULL g.code and is filtered out, so the query returns exactly the rows a plain JOIN would. Either the join type or the filter is wrong, and readability rules for joins and subqueries covers where filters on an outer-joined table belong.

Layout also makes diffs useful. If two versions of this query are each on one line, any change shows up as one changed line holding the entire query. Format both with identical settings, paste them into the diff checker, and a change from severity = 'high' to severity IN ('high', 'critical') becomes a small, located hunk:

     WHERE
-      severity = 'high'
+      severity IN ('high', 'critical')
   )

Use the same options on both sides; diffing an uppercase, trailing-comma version against a leading-comma one buries the real change under style noise. Better still, commit SQL already formatted so future diffs stay this small.

What the formatter leaves to you

  • Long lines. There is no line-width setting. A join with several conditions keeps them all on its ON line, so break long ones by hand.
  • BETWEEN. In a WHERE clause, the AND in BETWEEN 100 AND 500 gets its own line like a boolean AND. The query is unchanged, but it reads like an extra condition; Compact WHERE avoids it.
  • End-of-line comments. Comments are kept, but with trailing commas a -- note written after id, lands on the next line, above the following column. Put comments on their own line above what they describe.

Next steps

Paste your own query into the SQL formatter, agree on keyword case, comma style and the Compact WHERE setting with your team, and use those settings everywhere. Then look past layout: readability rules for joins and subqueries covers the choices no formatter makes for you.

Paste anything. devprix picks the tool.

Press ⌘K on any page, paste JSON, a JWT, a timestamp or a URL, and the right tool opens with your data filled in.

Browse all toolsSuggest a tool