Writing Readable SQL: Formatting Conventions, Aliasing Best Practices, and Structuring Complex Queries for Maintainability

Introduction

Picture this: you inherit a SQL query from a colleague who left the company six months ago. It's a single paragraph of text — no line breaks, no indentation, table names abbreviated to single letters, and subqueries nested three levels deep with no explanation of what they're doing. You need to modify it by end of day. You stare at it for twenty minutes and still aren't sure where the WHERE clause ends and the GROUP BY begins.

This is not a hypothetical. It happens constantly in data teams, and the culprit is almost never a lack of SQL knowledge — it's a lack of discipline around how that SQL is written. SQL is not just instructions for a database engine; it's communication between you and every future person who will need to understand, debug, or extend your work. That future person is often you, three months from now, with no memory of why you wrote it the way you did.

By the end of this lesson, you'll be able to write SQL that is immediately readable by any competent SQL practitioner, alias tables and columns in ways that add clarity rather than confusion, and structure complex queries — those with joins, subqueries, and common table expressions — so they can be understood and maintained without heroic effort.

What you'll learn:

• Core formatting conventions: capitalization, indentation, and line breaks that make SQL scannable
• How to alias tables and columns correctly, and when aliases help versus hurt readability
• How to break complex queries into logical sections using CTEs (Common Table Expressions)
• How to handle subqueries without creating an unreadable mess
• A practical review checklist you can apply to any query before you ship it

---

Prerequisites

You should be comfortable writing basic SELECT statements, using WHERE filters, joining two tables, and grouping data with GROUP BY. You don't need to be an expert — this lesson will explain every concept it introduces — but you should have written at least a few queries from scratch.

---

Why Formatting Matters More Than You Think

Before we get into the specifics, let's be honest about something: SQL engines don't care how you format your code. A query written on one line with no spaces between keywords will run exactly the same as a beautifully indented version. The formatting is entirely for human beings.

This makes formatting a professional skill, not a technical one. When you write unreadable SQL, you are creating debt for your team. Someone — maybe you — will pay that debt later in the form of time spent deciphering your intent. Formatting conventions exist to eliminate that tax.

The good news is that SQL is actually one of the easier languages to format consistently because its structure is rigid. Every query has a defined anatomy: SELECT, then FROM, then optional JOINs, then optional WHERE, then optional GROUP BY, then optional HAVING, then optional ORDER BY. Once you know that anatomy, you can apply a small set of rules that make every query consistent.

---

The Core Formatting Rules

Capitalization

The near-universal convention is to write SQL keywords in uppercase and object names in lowercase. Keywords are the words that are part of the SQL language itself: SELECT, FROM, WHERE, JOIN, ON, GROUP BY, ORDER BY, HAVING, LIMIT, AS, AND, OR, NOT, NULL, IS, IN, BETWEEN, LIKE, and so on. Object names are the things you or your team named: table names, column names, schema names, aliases.

Here's the same query written both ways:

-- Hard to scan: everything is the same visual weight
select order_id, customer_id, total_amount from orders where status = 'completed' and total_amount > 100;

-- Easy to scan: keywords stand out from object names
SELECT order_id, customer_id, total_amount
FROM orders
WHERE status = 'completed'
  AND total_amount > 100;

When you read the second version, your eye immediately picks out the structural skeleton: SELECT... FROM... WHERE. The keywords act like section headers. You can locate the WHERE clause in half a second without reading every word.

One Clause Per Line

Each major clause of a query should begin on its own line. "Major clause" means: SELECT, FROM, JOIN (each join), WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.

SELECT
    customer_id,
    COUNT(order_id)        AS order_count,
    SUM(total_amount)      AS lifetime_value
FROM orders
JOIN customers
    ON orders.customer_id = customers.id
WHERE orders.created_at >= '2024-01-01'
GROUP BY customer_id
ORDER BY lifetime_value DESC
LIMIT 100;

Notice that SELECT itself is alone on a line, and the columns are each on their own line below it, indented. This is a preference some teams share and others don't — some put the first column on the SELECT line. Either is fine as long as it's consistent. What matters is that each column gets its own line when there's more than one.

Indentation

Use consistent indentation to show hierarchy. The standard is either two or four spaces. Tabs can cause inconsistent rendering across editors and tools, so spaces are generally preferred.

The rule of thumb: anything that belongs to a clause is indented one level below it.

SELECT
    order_id,
    customer_id,
    total_amount
FROM orders
WHERE
    status = 'completed'
    AND total_amount > 100
    AND created_at >= '2024-01-01';

The AND conditions are indented to show they are all part of the WHERE clause. They're siblings, not nested inside each other.

Aligning ON Conditions with JOIN

When you join tables, put the ON condition on the next line, indented from the JOIN:

SELECT
    o.order_id,
    o.total_amount,
    c.email
FROM orders AS o
JOIN customers AS c
    ON o.customer_id = c.id
LEFT JOIN order_items AS oi
    ON o.order_id = oi.order_id;

This makes it visually obvious which condition belongs to which join. When you have five joins, you'll thank yourself for this habit.

Trailing Commas vs. Leading Commas

There are two camps on how to handle commas in a column list. Trailing commas put the comma at the end of each line:

SELECT
    customer_id,
    first_name,
    last_name,
    email
FROM customers;

Leading commas put the comma at the beginning:

SELECT
    customer_id
  , first_name
  , last_name
  , email
FROM customers;

The argument for leading commas is that they make it easier to spot a missing comma — you glance at the start of each line. The argument against is that it looks strange to most programmers coming from other languages. Pick one and be consistent within your team or project.

Tip: Many teams solve the trailing/leading comma debate by adopting a SQL formatter like sqlfluff or dbt's built-in formatting. These tools enforce your chosen style automatically on every save, removing the debate entirely.

---

Aliasing: The Art of Naming Things Well

Aliasing in SQL means giving something a temporary name for the duration of the query. You can alias both columns (to control what name appears in your results) and tables (to avoid repeating long names).

The syntax is straightforward:

-- Column alias
SELECT total_amount * 0.1 AS tax_amount
FROM orders;

-- Table alias
SELECT o.order_id
FROM orders AS o;

The AS keyword is optional in most databases — you can write orders o and it means the same thing — but you should always write AS explicitly. It makes it unambiguous that you're creating an alias rather than making a typo.

Table Aliases: Descriptive vs. Single-Letter

The single biggest aliasing mistake beginners make is abbreviating table names to single letters. You'll see queries like this in the wild:

-- Terrible: what is 'o', 'c', 'oi', 'p'?
SELECT o.order_id, c.email, oi.quantity, p.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.id;

After a certain number of tables, you lose track of what each letter stands for. Now try this:

-- Better: aliases are shortened but recognizable
SELECT
    orders.order_id,
    customers.email,
    order_items.quantity,
    products.name
FROM orders
JOIN customers
    ON orders.customer_id = customers.id
JOIN order_items
    ON orders.order_id = order_items.order_id
JOIN products
    ON order_items.product_id = products.id;

For simple queries with short table names, you can just use the full table name — no alias needed. Aliases should earn their place by genuinely reducing verbosity without sacrificing clarity.

When aliases are warranted (long table names, self-joins, multiple instances of the same table), make them meaningful abbreviations:

-- Acceptable aliases: still recognizable
SELECT
    ord.order_id,
    cust.email,
    items.quantity,
    prod.name
FROM orders AS ord
JOIN customers AS cust
    ON ord.customer_id = cust.id
JOIN order_items AS items
    ON ord.order_id = items.order_id
JOIN products AS prod
    ON items.product_id = prod.id;

Column Aliases: Document Your Intent

Column aliases serve a different purpose than table aliases. They let you rename a column in your result set — which is especially useful when you're computing something:

SELECT
    customer_id,
    COUNT(order_id)                          AS total_orders,
    SUM(total_amount)                        AS lifetime_revenue,
    AVG(total_amount)                        AS avg_order_value,
    MAX(created_at)                          AS most_recent_order_date
FROM orders
GROUP BY customer_id;

Without the aliases, your result set would have columns named COUNT(order_id), SUM(total_amount), etc. — which are accurate but ugly, and they break downstream tools that expect clean column names.

A few rules for column aliases:

• Make them descriptive of what the value means, not just what operation produced it
• Use snake_case (lowercase with underscores) to match your column naming convention
• Avoid spaces — while some databases allow AS "My Column" with quotes, it creates complications downstream
• Align the AS keyword vertically when you have multiple aliases — it makes them much easier to scan (as shown in the example above)

Warning: You cannot use a column alias in the WHERE clause of the same query. The database evaluates WHERE before it evaluates the SELECT list, so the alias doesn't exist yet. Use the alias in ORDER BY and HAVING, but repeat the full expression in WHERE.

---

Structuring Complex Queries with CTEs

When a query grows beyond a single SELECT with a few joins, you face a structural problem: how do you break it into manageable pieces? The answer, in modern SQL, is Common Table Expressions, or CTEs.

A CTE is a named, temporary result set that you define at the top of your query with the WITH keyword and can then reference like a table in the main query. Think of it like defining a variable before you use it.

Here's the anatomy:

WITH cte_name AS (
    -- This is the CTE body: a complete SELECT statement
    SELECT column_a, column_b
    FROM some_table
    WHERE some_condition
)
SELECT *
FROM cte_name;

You can chain multiple CTEs together:

WITH
cte_one AS (
    ...
),
cte_two AS (
    -- cte_two can reference cte_one
    SELECT *
    FROM cte_one
    WHERE ...
)
SELECT *
FROM cte_two;

A Real Example: Customer Revenue Tiers

Let's say you want to identify your top-tier customers — those whose lifetime revenue puts them in the top 20% — and then count how many orders they've placed in the last 90 days.

Without CTEs, you'd need a deeply nested subquery. With CTEs, you can solve it step by step:

WITH

-- Step 1: Calculate each customer's lifetime revenue
customer_lifetime_revenue AS (
    SELECT
        customer_id,
        SUM(total_amount) AS lifetime_revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
),

-- Step 2: Identify top-tier customers (top 20% by revenue)
top_tier_customers AS (
    SELECT
        customer_id,
        lifetime_revenue
    FROM customer_lifetime_revenue
    WHERE lifetime_revenue >= (
        SELECT PERCENTILE_CONT(0.80) WITHIN GROUP (ORDER BY lifetime_revenue)
        FROM customer_lifetime_revenue
    )
),

-- Step 3: Count recent orders for those customers
recent_order_activity AS (
    SELECT
        orders.customer_id,
        COUNT(orders.order_id) AS orders_last_90_days
    FROM orders
    JOIN top_tier_customers
        ON orders.customer_id = top_tier_customers.customer_id
    WHERE orders.created_at >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY orders.customer_id
)

-- Final result: combine everything
SELECT
    top_tier_customers.customer_id,
    top_tier_customers.lifetime_revenue,
    COALESCE(recent_order_activity.orders_last_90_days, 0) AS orders_last_90_days
FROM top_tier_customers
LEFT JOIN recent_order_activity
    ON top_tier_customers.customer_id = recent_order_activity.customer_id
ORDER BY top_tier_customers.lifetime_revenue DESC;

Notice what happened here. A query that would have required three levels of nested subqueries is now readable as a sequence of clearly labeled steps. Each CTE has a comment explaining its purpose. The final SELECT is clean and simple.

Tip: Name your CTEs after what they contain, not after what they do. customer_lifetime_revenue (a noun phrase describing the data) is better than calculate_lifetime_revenue (a verb phrase describing an action). You'll be querying from it like a table, so it should read like a table name.

---

Handling Subqueries When You Have To

Sometimes you'll encounter or write subqueries — a SELECT statement nested inside another statement. CTEs are almost always preferable for readability, but subqueries appear in contexts where CTEs can't go: inside WHERE clauses for existence checks, or inline in a SELECT list for scalar values.

When you must use a subquery, format it so it's clearly delimited:

-- Poorly formatted subquery: where does it start and end?
SELECT customer_id, email FROM customers WHERE customer_id IN (SELECT customer_id FROM orders WHERE total_amount > 500);

-- Well formatted subquery: clear structure
SELECT
    customer_id,
    email
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE total_amount > 500
);

The key rule: indent the inner query one level deeper than its surrounding context, and put the closing parenthesis on its own line at the same indentation level as the opening clause.

---

A Pre-Ship Review Checklist

Before you share or commit a SQL query, run through this list:

1. Keywords capitalized? Every SQL keyword should be uppercase.
2. Columns on separate lines? Each column in a SELECT list gets its own line when there are two or more.
3. JOIN conditions indented? Each ON clause is indented below its JOIN.
4. Aliases meaningful? No single-letter table aliases unless it's a very short, obvious query. Column aliases describe what the value means.
5. AS written explicitly? Don't rely on implicit aliasing.
6. Complex logic broken into CTEs? Anything requiring more than two levels of nesting should become a CTE chain.
7. Comments on non-obvious steps? If a CTE or filter requires context, add a -- comment above it.
8. Trailing semicolon? End your query with a semicolon.

---

Hands-On Exercise

You're given the following query written by a former colleague. It works, but it's unreadable. Your job is to reformat and restructure it without changing what it does.

select e.id, e.name, d.name, count(p.id) as proj_count, avg(p.budget) as avg_bud from employees e join departments d on e.dept_id=d.id left join projects p on e.id=p.lead_employee_id where e.hire_date >= '2020-01-01' and d.name != 'HR' group by e.id, e.name, d.name having count(p.id) > 2 order by avg_bud desc;

Steps to complete:

1. Apply proper capitalization to all SQL keywords.
2. Put each clause on its own line with proper indentation.
3. Replace the single-letter table aliases with descriptive ones.
4. Add meaningful column aliases where the output column names would be unclear.
5. As a bonus: refactor the query to use a CTE that pre-filters employees hired after 2020, then uses that CTE in the main query.

Reference solution (try it yourself first):

-- Basic reformatting
SELECT
    employees.id            AS employee_id,
    employees.name          AS employee_name,
    departments.name        AS department_name,
    COUNT(projects.id)      AS project_count,
    AVG(projects.budget)    AS avg_project_budget
FROM employees
JOIN departments
    ON employees.dept_id = departments.id
LEFT JOIN projects
    ON employees.id = projects.lead_employee_id
WHERE
    employees.hire_date >= '2020-01-01'
    AND departments.name != 'HR'
GROUP BY
    employees.id,
    employees.name,
    departments.name
HAVING COUNT(projects.id) > 2
ORDER BY avg_project_budget DESC;


-- Bonus: CTE version
WITH

post_2020_employees AS (
    SELECT id, name, dept_id, hire_date
    FROM employees
    WHERE hire_date >= '2020-01-01'
)

SELECT
    post_2020_employees.id            AS employee_id,
    post_2020_employees.name          AS employee_name,
    departments.name                  AS department_name,
    COUNT(projects.id)                AS project_count,
    AVG(projects.budget)              AS avg_project_budget
FROM post_2020_employees
JOIN departments
    ON post_2020_employees.dept_id = departments.id
LEFT JOIN projects
    ON post_2020_employees.id = projects.lead_employee_id
WHERE departments.name != 'HR'
GROUP BY
    post_2020_employees.id,
    post_2020_employees.name,
    departments.name
HAVING COUNT(projects.id) > 2
ORDER BY avg_project_budget DESC;

---

Common Mistakes & Troubleshooting

"My query runs fine but my teammate can't follow the logic." The most common cause: logic is hidden inside a multi-level subquery. Extract each logical step into a named CTE. Give each CTE a comment explaining what it represents. Your teammate should be able to read the CTE names alone and understand the structure of your approach.

"I used my column alias in the WHERE clause and got an error." This is one of the most frequent SQL gotchas. The database processes WHERE before SELECT, so your alias doesn't exist when WHERE runs. Repeat the full expression in WHERE, or move the filter to a HAVING clause if it's a post-aggregation filter — but understand that HAVING runs after GROUP BY, so it has a different performance profile.

"My single-letter aliases worked fine in my head while writing the query, but now I can't read it." This is almost universal. Single-letter aliases feel efficient while you're writing but become a liability the moment you step away. Adopt the habit of writing full or abbreviated descriptive aliases from the start — it takes an extra ten seconds and saves minutes of re-orientation later.

"My CTEs are getting so long the query feels unwieldy." If a single CTE body is longer than 15-20 lines, that's a signal it might be doing too much. Try splitting it into two CTEs. Also consider whether this logic belongs in a database view — a view is essentially a named CTE that persists and can be reused across many queries.

"I can't decide whether to use a CTE or a subquery." Default to CTEs for anything that needs a name and is referenced more than once, or for any logic that would otherwise create more than one level of nesting. Use subqueries only for simple, one-off filters (WHERE id IN (SELECT ...)) where creating a CTE would be more ceremony than it's worth.

---

Summary & Next Steps

Writing readable SQL is a habit, not a talent. The conventions covered in this lesson — uppercase keywords, consistent indentation, one clause per line, meaningful aliases, and CTEs for complex logic — are simple rules that compound over time. Apply them consistently and your queries become documents that communicate intent, not just instructions that produce output.

The key takeaways:

• Format for the reader, not the engine. The database doesn't care about your indentation; your colleagues do.
• Aliases should add clarity, not replace the names you've already forgotten. Use descriptive table aliases and intent-driven column aliases.
• CTEs are your primary tool for managing complexity. Name them like tables, comment their purpose, and chain them in logical sequence.
• A query you can't read in a year is a query you'll have to rewrite. Invest 10% more time in formatting and save 10x that time in future debugging.

Where to go next:

• Learn about window functions, which produce complex multi-column outputs that benefit enormously from aliasing conventions
• Explore database views, the persistent cousin of CTEs that lets you share formatted, named logic across multiple queries and users
• Practice refactoring: find the ugliest query in your current project and apply the checklist in this lesson to it — the exercise will cement every concept covered here