How to Format Complex SQL: JOINs, Subqueries, and CTEs Without Losing Readability

Indentation and capitalization alone can't make a five-JOIN query with a nested subquery readable — past a certain complexity, the fix isn't better formatting, it's restructuring the query with CTEs (WITH clauses) so each logical step gets its own named, readable block instead of one deeply nested statement.

Why JOINs degrade readability fast

Two or three JOINs format cleanly — one line per JOIN, consistent indentation, done. The problem starts around four or five: the ON conditions multiply, column names from different tables start looking interchangeable, and a reader has to mentally track which table each column actually came from. A formatter can indent this consistently, but it can't fix the underlying fact that the query is doing too much in one SELECT.

Subqueries nested in the WHERE or FROM clause are worse

A subquery buried inside WHERE column IN (SELECT ...) or used as a derived table in FROM forces a reader to parse the query inside-out — understand the innermost subquery first, then work back out to see how it's used. Formatters generally indent nested subqueries by level, which helps, but the structural problem (reading order doesn't match execution logic) remains no matter how it's indented.

CTEs restructure the problem, not just the formatting

A Common Table Expression (the WITH clause) lets you name each logical step — WITH active_users AS (...), recent_orders AS (...) — and then write a final SELECT that reads top-to-bottom in the same order a person would explain the query out loud. This doesn't change what the database executes in most cases; it changes what a human has to hold in their head while reading it. Breaking a nested subquery out into a named CTE is almost always worth doing once a query needs more than one level of nesting.

What to actually do

• Up to 2-3 JOINs: a formatter alone (consistent indentation, one JOIN per line) is enough
• 4+ JOINs or any nested subquery: restructure with CTEs first, then format the result — formatting a badly-structured query just produces a longer badly-structured query
• Name each CTE for what it represents (recent_orders, not cte1) — the names are what make the final query readable
• Keep the final SELECT simple — if it still needs its own nested logic, that's a sign another CTE step is missing