ಸಂಕೀರ್ಣ SQL ಕ್ವೆರಿಗಳನ್ನು ಫಾರ್ಮ್ಯಾಟ್ ಮಾಡುವುದು ಹೇಗೆ

How to lay out queries that fill a screen or more: CTE chains, nested subqueries, CASE expressions, window functions and UNION. Each section shows what SQL Format produces and the manual habits that make the result easier to follow.

A short query is readable in almost any layout. A long one is only readable if its shape tells you where each part starts and ends. Formatting cannot fix a query that does too many things at once, but it can make the structure visible, and a visible structure is what lets you spot the part that needs rewriting. The examples below use the default options of the formatter: four-space indentation, trailing commas and uppercase keywords.

General principles

• One idea per block. Each CTE, subquery or CASE expression should do one thing you could describe in a short sentence.
• Indentation shows nesting. Everything inside a pair of parentheses should be indented relative to the line that opened it, so you can find the matching close by looking straight down.
• Read top to bottom. Prefer structures that can be read in order (named steps) over structures that have to be read inside out (deep nesting).
• Names are documentation. A good CTE name or column alias replaces a comment.

CTEs: a chain of named steps

A common table expression lets you split a query into named steps, each one building on the previous ones. Think of it as a pipeline: filter the raw rows, aggregate them, rank them, then select the final columns. Here is a three-step chain written on one line and formatted:

with paid_orders as (select order_id, customer_id, total_amount, created_at from orders where status = 'paid' and created_at >= date '2026-01-01'), customer_revenue as (select customer_id, count(*) as order_count, sum(total_amount) as revenue from paid_orders group by customer_id), ranked_customers as (select customer_id, order_count, revenue, RANK() over (order by revenue desc) as revenue_rank from customer_revenue) select c.customer_name, r.order_count, r.revenue, r.revenue_rank from ranked_customers r join customers c on c.customer_id = r.customer_id where r.revenue_rank <= 10 order by r.revenue_rank;

WITH paid_orders AS (
    SELECT order_id,
        customer_id,
        total_amount,
        created_at
    FROM orders
    WHERE status = 'paid'
    AND created_at >= DATE '2026-01-01'
),
customer_revenue AS (
    SELECT customer_id,
        COUNT(*) AS order_count,
        SUM(total_amount) AS revenue
    FROM paid_orders
    GROUP BY customer_id
),
ranked_customers AS (
    SELECT customer_id,
        order_count,
        revenue,
        RANK() OVER (
            ORDER BY revenue DESC
        ) AS revenue_rank
    FROM customer_revenue
)
SELECT c.customer_name,
    r.order_count,
    r.revenue,
    r.revenue_rank
FROM ranked_customers r
    JOIN customers c
        ON  c.customer_id = r.customer_id
WHERE r.revenue_rank <= 10
ORDER BY r.revenue_rank;

The formatter has no layout specific to CTEs; it applies the same rules it uses everywhere else. The body of each CTE is a subquery, so it is indented inside its parentheses. The closing parenthesis returns to the left margin, the comma between CTEs follows it on the same line, and the next CTE name starts a new line. With leading commas, the comma moves to the start of the next name instead (,customer_revenue AS (). With the Parentheses on new line option turned on, the opening parenthesis after AS moves to its own line instead, which some people find easier to match visually.

What the formatter cannot do for you is the structure itself. A few habits make CTE chains much easier to read:

• Name each step for its result, not for how it is built: paid_orders, customer_revenue, ranked_customers. Avoid cte1, tmp or base.
• One transformation per CTE. If a step filters, joins, aggregates and ranks, split it. The extra lines cost less than the time a reviewer spends untangling it.
• Keep the final SELECT short. Ideally it only joins the last step to lookup tables and picks the output columns.
• Select only the columns the next step needs. That keeps each step honest about its inputs and makes unused columns obvious.
• Debug step by step. A named chain lets you replace the final SELECT with SELECT * FROM customer_revenue to inspect an intermediate result.

Nested subqueries

Subqueries in WHERE, especially after IN, are often the clearest way to express a filter. They become hard to read when they nest. The formatter indents each level inside its parentheses, so the depth is at least visible:

select p.product_id, p.product_name from products p where p.category_id in (select category_id from categories where parent_id in (select category_id from categories where category_name = 'Hardware')) and p.discontinued = false;

SELECT p.product_id,
    p.product_name
FROM products p
WHERE p.category_id IN (
    SELECT category_id
    FROM categories
    WHERE parent_id IN (
        SELECT category_id
        FROM categories
        WHERE category_name = 'Hardware'
    )
)
AND p.discontinued = false;

Each subquery opens with a parenthesis at the end of the IN line, and its closing parenthesis lines up with the start of the line that opened it. The last condition, AND p.discontinued = false, sits back at the outer level, which makes it obvious that it belongs to the outer query and not to the subquery. Note that TRUE and FALSE are always written in lowercase by this formatter.

If you prefer each opening parenthesis on its own line, the Parentheses on new line option gives this layout:

SELECT p.product_id,
    p.product_name
FROM products p
WHERE p.category_id IN
(
    SELECT category_id
    FROM categories
    WHERE parent_id IN
    (
        SELECT category_id
        FROM categories
        WHERE category_name = 'Hardware'
    )
)
AND p.discontinued = false;

Two levels of nesting is usually where readability starts to suffer. At that point, consider moving the inner query into a CTE with a descriptive name (here, something like hardware_categories), or replacing a chain of IN subqueries with joins when the relationship is simple.

Correlated subqueries and EXISTS

Correlated subqueries, which refer to a column of the outer query, deserve extra care because the link between the two levels is easy to miss. Put the correlating condition first in the inner WHERE and qualify both sides with their aliases. The formatter keeps NOT EXISTS on the condition line, as AND NOT EXISTS (, and indents the subquery below it like any other:

select c.customer_id, c.customer_name from customers c where c.is_active = true and not exists (select 1 from orders o where o.customer_id = c.customer_id and o.created_at >= current_date - 365)

SELECT c.customer_id,
    c.customer_name
FROM customers c
WHERE c.is_active = true
AND NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
    AND o.created_at >= CURRENT_DATE - 365
)

The SELECT 1 is a convention: EXISTS only checks whether a row exists, so the select list is irrelevant, and writing a constant makes that clear to the reader.

CASE expressions

A CASE expression with several branches packed on one line is one of the hardest things to read in SQL. The formatter gives CASE its own layout, shown here for a searched CASE (conditions after each WHEN) and a simple CASE (one value compared with constants):

select order_id, total_amount, case when total_amount >= 1000 then 'large' when total_amount >= 100 then 'medium' else 'small' end as order_size, case status when 'open' then 1 when 'shipped' then 2 else 3 end as status_rank from orders;

SELECT order_id,
    total_amount,
    CASE WHEN total_amount >= 1000 THEN
        'large'
    WHEN total_amount >= 100 THEN
        'medium'
    ELSE
        'small'
    END AS order_size,
    CASE status WHEN 'open' THEN
        1
    WHEN 'shipped' THEN
        2
    ELSE
        3
    END AS status_rank
FROM orders;

The first WHEN stays on the CASE line and each following WHEN starts a new line, always together with its condition and THEN. The result goes on the next line, indented one level, and ELSE and END line up with the WHEN branches. The alias follows END, so the name of the computed column is easy to find.

Manual tips for CASE:

• Order the branches deliberately. A searched CASE returns the first matching branch, so put the most specific condition first. In the example, >= 1000 must come before >= 100.
• Always write an ELSE. Without it, unmatched rows return NULL. If that is intended, write ELSE NULL so the reader knows it was not forgotten.
• Keep result types consistent. Mixing numbers and strings across branches leads to conversion errors or surprising implicit casts.
• Move big mappings out of the query. A CASE with twenty branches that maps codes to labels is usually a lookup table waiting to be created.

Window functions

Window functions compute a value across a set of related rows without collapsing them, which makes them ideal for rankings, running totals and comparisons with the previous row. Their OVER clause can get long, with a partition, an order and a frame. Here is how the formatter lays them out:

select customer_id, order_id, total_amount, ROW_NUMBER() over (partition by customer_id order by created_at desc) as recency_rank, sum(total_amount) over (partition by customer_id order by created_at ROWS between UNBOUNDED PRECEDING and CURRENT ROW) as running_total from orders where status <> 'cancelled';

SELECT customer_id,
    order_id,
    total_amount,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY created_at DESC
    ) AS recency_rank,
    SUM(total_amount) OVER (
        PARTITION BY customer_id
        ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders
WHERE status <> 'cancelled';

OVER ( stays on the same line as the function, and the window specification is indented below it: PARTITION BY and ORDER BY each start their own line, and the closing parenthesis returns to the indentation of the function, followed by the alias. The frame clause (ROWS BETWEEN ...) stays on the ORDER BY line, and its words (ROWS, UNBOUNDED PRECEDING, CURRENT ROW) are re-cased like any other keyword. Window function names such as ROW_NUMBER and RANK are not treated as keywords and keep the case you typed, so write them consistently by hand.

Manual tips for window functions:

• Always alias the result with a name that says what it means: recency_rank, running_total, not rn or sum1.
• Be explicit about the frame when you use an aggregate with ORDER BY. The default frame is easy to get wrong when there are ties in the ordering column.
• Reuse a window definition. When several functions share the same PARTITION BY and ORDER BY, databases that support the named WINDOW clause, PostgreSQL among them, let you define it once and reference it by name.
• Filter in an outer step. Window functions cannot be used in WHERE at the same level. To keep only the top row per group, compute the rank in a CTE and filter on it in the next step.

UNION and UNION ALL

Set operations combine the results of several SELECT statements. The key to reading them is seeing where one branch ends and the next begins:

select customer_id, 'order' as event_type, created_at from orders where created_at >= current_date - 7 union all select customer_id, 'refund' as event_type, created_at from refunds where created_at >= current_date - 7 union select customer_id, 'signup' as event_type, created_at from customers where created_at >= current_date - 7 order by created_at;

SELECT customer_id,
    'order' AS event_type,
    created_at
FROM orders
WHERE created_at >= CURRENT_DATE - 7
UNION ALL
SELECT customer_id,
    'refund' AS event_type,
    created_at
FROM refunds
WHERE created_at >= CURRENT_DATE - 7
UNION
SELECT customer_id,
    'signup' AS event_type,
    created_at
FROM customers
WHERE created_at >= CURRENT_DATE - 7
ORDER BY created_at;

Each UNION or UNION ALL sits on its own line at the left margin, between two SELECT blocks that keep their normal layout. The final ORDER BY appears after the last branch. It sorts the combined result, not the last branch only, which is easy to misread if the branches are not clearly separated.

Manual tips for set operations:

• Choose UNION ALL by default. UNION removes duplicate rows, which costs a sort or hash and can hide data problems. Use it only when you actually need deduplication, and consider a comment saying so.
• Mixing UNION and UNION ALL, as in the example, is legal but subtle: the deduplication applies to the result built so far, not only to the last branch. When it is not obvious, split the query into CTEs.
• Align the select lists. Columns are matched by position, not by name. Give every branch the same columns in the same order, with the same aliases, and add a literal column such as event_type to say where each row came from.
• Keep each branch simple. If a branch needs its own joins and filters, consider a CTE per branch and a final UNION of the CTEs.

Long lines and wrapping

Even with good structure, some lines get long: a long arithmetic expression, a function with many arguments, a long list of conditions. The Maximum columns option, 120 by default, breaks any output line longer than the limit at the last space before it and indents the continuation. It is a final pass on the finished output, so it does not rearrange clauses. If a line is still hard to read after wrapping, that is a signal to introduce a CTE or an intermediate alias rather than to lower the limit.

A quick checklist for complex queries

• Can each CTE be described in one short sentence, and does its name say what it returns?
• Is any subquery nested more than two levels deep?
• Does every CASE have an ELSE, and are its branches in the right order?
• Does every window function have a meaningful alias and an explicit frame where it matters?
• Is every UNION (without ALL) really meant to remove duplicates?
• Are all columns qualified with a table alias?

For the conventions behind these tips, see the SQL style guide. If your queries use PostgreSQL features such as :: casts or dollar-quoted function bodies, the PostgreSQL formatting guide covers them, and common SQL readability mistakes lists patterns worth fixing before you format.