How to Format Long SQL Queries for Readability

Every team has one: the 150-line report query that everybody is afraid to touch. It works — probably — but nobody can say exactly why, and every change feels like defusing a bomb. Long queries do not have to be like this. This guide covers five techniques that turn sprawling SQL into something a new teammate can read top to bottom, and then applies all of them to one messy query in a step-by-step refactor.

Technique 1: Break the Query into CTEs

The single most powerful readability tool in SQL is the common table expression. A CTE gives a name to an intermediate result, so instead of mentally unwinding nested subqueries from the inside out, the reader follows a top-to-bottom pipeline:

WITH active_users AS (
  SELECT id, name, signup_date
  FROM users
  WHERE status = 'active'
),
recent_orders AS (
  SELECT user_id, COUNT(*) AS order_count
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
  GROUP BY user_id
)
SELECT au.name, COALESCE(ro.order_count, 0) AS orders_90d
FROM active_users au
LEFT JOIN recent_orders ro ON ro.user_id = au.id;

Each CTE should do one job and have a name that describes its output, not its mechanics: recent_orders, not subquery_2. For a full introduction, see our CTE guide.

Technique 2: One JOIN Per Line, One Condition Per Line

Horizontal sprawl is the enemy. When three JOINs and their ON conditions share two lines, a missing condition is invisible. Give every JOIN its own line, its ON clause indented beneath it, and every AND in a WHERE clause its own line:

FROM orders o
JOIN customers c
  ON c.id = o.customer_id
LEFT JOIN coupons cp
  ON cp.id = o.coupon_id
WHERE o.status = 'completed'
  AND o.order_date >= '2026-01-01'
  AND c.country = 'IN'

This layout also produces clean diffs: adding a JOIN or a filter changes exactly one line, which makes code review dramatically easier.

Technique 3: Extract Deeply Nested Subqueries

A subquery inside a subquery inside a WHERE clause forces the reader to hold three contexts at once. If a subquery is more than a couple of lines, promote it to a CTE. If it appears twice, promoting it also removes duplication — one definition, referenced by name wherever needed.

• Nesting depth of 1 (a simple IN or EXISTS) is usually fine inline.
• Nesting depth of 2 or more is a strong signal to extract.
• Derived tables in the FROM clause (FROM (SELECT ...) x) almost always read better as CTEs.

Technique 4: Comment Headers and Vertical Whitespace

Long queries deserve the same structure as long functions: a short header explaining purpose and gotchas, plus blank lines between logical sections. Comments should say why, not restate the SQL:

-- Monthly revenue report per region.
-- Note: refunds are excluded here; finance handles them separately.

WITH completed_orders AS (
  ...
),

-- Refunded orders are excluded above, so this is gross revenue.
regional_totals AS (
  ...
)

SELECT ...

Worked Example: Refactoring a Messy Query

Here is a condensed version of the kind of query that accumulates in every reporting codebase — everything inline, everything on as few lines as possible:

select c.region, count(distinct o.id) as orders, sum(o.total) as revenue,
(select avg(total) from orders where status='completed') as global_avg
from orders o join customers c on c.id=o.customer_id
where o.status='completed' and o.id not in (select order_id from refunds)
and c.id in (select customer_id from subscriptions where plan!='free')
group by c.region having sum(o.total)>10000 order by revenue desc;

Step 1 — name the filters as CTEs. The two IN/NOT IN subqueries are really business concepts: refunded orders and paying customers. Give them names.

Step 2 — reshape the main query. One JOIN per line, one condition per line, uppercase keywords (or lowercase — just be consistent, as we discuss in the casing debate).

Step 3 — add a header and whitespace. The result:

-- Revenue by region for paying customers, excluding refunded orders.
-- Only regions above 10k revenue are included (reporting threshold).

WITH refunded_orders AS (
  SELECT order_id
  FROM refunds
),

paying_customers AS (
  SELECT customer_id
  FROM subscriptions
  WHERE plan <> 'free'
),

global_avg AS (
  SELECT AVG(total) AS avg_order_value
  FROM orders
  WHERE status = 'completed'
)

SELECT
  c.region,
  COUNT(DISTINCT o.id)     AS orders,
  SUM(o.total)             AS revenue,
  g.avg_order_value        AS global_avg
FROM orders o
JOIN customers c
  ON c.id = o.customer_id
CROSS JOIN global_avg g
WHERE o.status = 'completed'
  AND o.id NOT IN (SELECT order_id FROM refunded_orders)
  AND c.id IN (SELECT customer_id FROM paying_customers)
GROUP BY c.region, g.avg_order_value
HAVING SUM(o.total) > 10000
ORDER BY revenue DESC;

The refactored version is longer in lines but far shorter in reading time. Each block answers one question, and a reviewer can verify the refund exclusion or the paying customer filter without touching the rest of the query.

Frequently Asked Questions