Somewhere in your codebase there's a query that everybody is afraid of.

It's 60 lines long. It has three nested subqueries, tables aliased as a, b and c, a CASE inside a SUM inside another CASE, and a WHERE clause with a magic status = 3 that nobody can explain. It works. Nobody touches it. And when the numbers on the report look wrong, everyone quietly hopes it's someone else's turn.

The person who wrote it wasn't careless. They were in the middle of the problem, and in the middle of a problem, everything makes sense. SQL readability is about the version of you who opens that file six months later with none of that context.

Start with formatting, because it's free

Most "unreadable" SQL is really "unformatted" SQL. A few habits fix a surprising amount:

• One clause per line. SELECT, FROM, each JOIN, WHERE, GROUP BY, ORDER BY each start a new line.
• One column per line once you have more than three or four. It makes diffs readable too: adding a column changes one line.
• Join conditions next to the join, not hidden in the WHERE clause.
• Pick a keyword case and stick to it. Uppercase keywords are the common convention, and they separate the language from your table names at a glance.

Here's the same query, before and after. Nothing about the logic changed:

select c.id,t.name,sum(p.amount) from contracts c join tenants t on t.id=c.tenant_id left join payments p on p.contract_id=c.id where c.status=3 group by c.id,t.name

SELECT
    contracts.id      AS contract_id,
    tenants.name      AS tenant_name,
    SUM(payments.amount) AS total_paid
FROM contracts
JOIN tenants        ON tenants.id = contracts.tenant_id
LEFT JOIN payments  ON payments.contract_id = contracts.id
WHERE contracts.status = 'active'
GROUP BY contracts.id, tenants.name;

Notice two quieter changes: real table names instead of one-letter aliases, and 'active' instead of 3. Short aliases are fine for long table names (maintenance_requests AS requests), but a, b, c forces the reader to keep a lookup table in their head.

Name things like you name variables

Column aliases are documentation. SUM(amount) in a result set tells you nothing; total_paid tells you exactly what you're looking at, and it's what your application code will read.

The same goes for the schema itself. A column called flag or type2 will haunt every query that touches it. If a status is a number, either store a readable value or keep a well-known mapping in the codebase, never just in someone's memory.

CTEs: give each step a name

The biggest upgrade for complex queries is the common table expression, the WITH clause. It lets you split a query into named steps that read top to bottom, like a small program. MySQL supports them from 8.0, and PostgreSQL and SQL Server have had them for years.

Say you want tenants whose overdue balance is above a limit. The nested-subquery version reads inside out. The CTE version reads like a recipe:

WITH due AS (
    SELECT contract_id, SUM(amount) AS total_due
    FROM invoices
    WHERE due_date < CURRENT_DATE
    GROUP BY contract_id
),
paid AS (
    SELECT contract_id, SUM(amount) AS total_paid
    FROM payments
    GROUP BY contract_id
)
SELECT
    due.contract_id,
    due.total_due - COALESCE(paid.total_paid, 0) AS overdue
FROM due
LEFT JOIN paid ON paid.contract_id = due.contract_id
WHERE due.total_due - COALESCE(paid.total_paid, 0) > 500;

Each block answers one question: what's due, what's paid, what's the difference. You can also debug it piece by piece: run just the due step and check its numbers before blaming the final query.

The COALESCE is there because a contract with no payments gets NULL from the left join, and anything minus NULL is NULL. That's exactly the kind of detail that's easy to miss when the logic is buried three levels deep.

Stop writing SELECT * in real code

SELECT * is great for poking around in a console. In application code it causes three slow problems:

• It hides intent. The reader can't see which columns actually matter.
• It moves more data than needed, including that large TEXT or JSON column someone added last year.
• It changes behaviour silently. Add a column, and every query returns it. With joins, two tables with an id or created_at column can overwrite each other in your result array depending on your driver.

In Laravel, the same idea is ->select(['id', 'name', 'status']) on queries that run often or return many rows.

Comments: explain why, not what

SQL needs comments more than most code, because business rules hide in it. Don't comment -- join payments; the reader can see that. Comment the thing they can't see:

-- Cancelled contracts still count until their end date:
-- tenants can be billed for the notice period.
WHERE contracts.status IN ('active', 'cancelled')
  AND contracts.end_date >= CURRENT_DATE

That comment will save someone from "fixing" a bug that isn't one.

Window functions: the tool that removes self-joins

Once your queries are readable, window functions are the next step up. They let you calculate across related rows without collapsing them the way GROUP BY does. Classic example: the latest payment per contract.

WITH ranked AS (
    SELECT
        payments.*,
        ROW_NUMBER() OVER (
            PARTITION BY contract_id
            ORDER BY paid_at DESC
        ) AS rn
    FROM payments
)
SELECT contract_id, amount, paid_at
FROM ranked
WHERE rn = 1;

PARTITION BY says "restart the numbering for each contract", and ORDER BY says "newest first". The older alternative is a self-join or a correlated subquery, which is harder to read and easier to get subtly wrong when two payments share a timestamp. (Yes, there's a payments.* inside the CTE; inside a step that you immediately narrow down, it's a reasonable shortcut.)

Other window functions worth knowing: SUM(...) OVER (...) for running totals, LAG() to compare a row with the previous one, and RANK() for leaderboards with ties. MySQL 8.0+ supports them all.

Checklist

• One clause per line, joins with their conditions.
• Readable aliases, for tables and for result columns.
• CTEs for anything with more than one logical step.
• Named columns instead of SELECT * in application code.
• Comments that explain business rules, not syntax.
• Window functions instead of clever self-joins.

What's the worst query you've inherited, and what was the first thing you did to make sense of it?