SELECT *, mystery aliases, and the other habits that turn working SQL into queries nobody wants to touch — with concrete fixes for each.
Every team has that query. The 300-liner in the reporting codebase, the "quick" analytics script someone wrote three years ago that now runs in production every night. You open it, scroll through it, realize you can't tell which alias maps to which table, can't parse what status = 3 actually means, can't find where the subquery ends and the outer SELECT resumes — so you close it and go ask the person who wrote it. Who may have left. Unreadable queries don't fail. They just cost everyone 40 minutes every time someone needs to touch them, quietly, forever.
The good news: the habits that produce unreadable SQL are specific and fixable. Here's what's actually causing the problem.
1. SELECT * is a lie about your data contract
SELECT * is the fastest thing to type and the slowest thing to maintain. When you see it in a query, you've lost the schema at a glance — you don't know how many columns are coming back, which ones the calling code actually reads, or what breaks when someone adds a column to the upstream table next month.
The concrete failure modes are worse than they sound. In PostgreSQL, a view built on SELECT * may or may not reflect later ALTER TABLE changes depending on how the view was materialized. More immediately: a SELECT * across a JOIN can return duplicate column names — both tables have an id column, so the result set has two id columns — and any client that addresses columns by index rather than name will silently start returning the wrong one.
Name every column. All twelve of them if there are twelve. The first line of a SELECT is the data contract: it tells the next reader exactly what the query is claiming about the world. SELECT * claims nothing.
2. Mystery aliases — t1, u, a, b
Table aliases exist for a reason: long table names are verbose in a JOIN ... ON clause, and a well-chosen alias reduces noise. The key phrase is "well-chosen." An alias should abbreviate, not obscure.
-- what you write on a deadline
SELECT t1.id, t2.name, t3.total
FROM orders t1
JOIN users t2 ON t1.user_id = t2.id
JOIN invoices t3 ON t1.id = t3.order_id
-- what the next person reads
SELECT o.id, u.name, inv.total
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN invoices inv ON o.id = inv.order_idt1 could be any table in the schema. o can only be orders. The second version is instantly readable; the first requires constant cross-referencing between the FROM clause and the SELECT list. When a query joins six tables, that cognitive overhead multiplies fast. Use the first letter or a recognizable abbreviation — not a, b, c in sequence, which tells you nothing except the order the author wrote the JOINs.
3. Magic constants in WHERE
What does WHERE status = 3 mean? If you wrote it on the day you built the schema, you know. Six months later, nobody does. Magic literals — bare integers or strings used as conditions without any indication of what they represent — are the comment debt of SQL. The query runs. The intent is gone.
The fix depends on your stack. If you have an enum type, use its labels. If you have a lookup table, join to it and filter by name. If you're stuck with numeric status codes and can't change the schema, a comment directly above the WHERE clause is better than silence:
-- status 3 = payment_confirmed (see payments.status enum in schema.sql)
WHERE status = 3SQL doesn't have named constants or inline enum lookups the way most application languages do, so the burden falls on the writer to document intent at the point of use. The reader shouldn't have to open the schema file to decode a filter condition.
4. The one-liner compression reflex
This is the most visually obvious problem and the easiest to fix. Queries written as a single long expression — or with all clauses run together without line breaks — force the reader to parse horizontally instead of letting the query's structure be visible at a glance.
-- written for terseness
SELECT u.email, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.created_at > '2025-01-01' GROUP BY u.email HAVING COUNT(o.id) > 5 ORDER BY order_count DESC
-- written to be read
SELECT u.email,
COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2025-01-01'
GROUP BY u.email
HAVING COUNT(o.id) > 5
ORDER BY order_count DESCClause keywords on their own line — SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY — turn the query into something you can scan vertically. The WHERE condition, the GROUP BY columns, the HAVING filter are all immediately locatable. The one-liner version requires a full horizontal read every time.
5. Nested subqueries where a CTE belongs
Subqueries are fine. Deeply nested anonymous subqueries — a SELECT inside a SELECT inside a SELECT, all inlined — are where query readability goes to die. The inner logic has no name, so you can't tell what it represents without parsing the whole thing from the inside out.
"A common table expression (CTE) in SQL is a named subquery defined with the WITH clause that provides a way to improve the organization of a query."
— Wikipedia, "Hierarchical and recursive queries in SQL" (CC BY-SA 4.0)
A CTE gives a name to each logical step. Instead of "here is a subquery that does something", it says "this is called confirmed_orders and the outer query then uses it." The query reads like a series of named steps rather than a parsing puzzle. CTEs are supported in PostgreSQL since 8.4, MySQL since 8.0, and SQLite since 3.35 — there's no good reason to nest anonymous subqueries three levels deep in any modern database.
-- inline subquery — what does the inner SELECT mean?
SELECT u.email, sub.order_count
FROM users u
JOIN (SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id) sub
ON u.id = sub.user_id
-- CTE — the intent is in the name
WITH order_counts AS (
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
)
SELECT u.email, oc.order_count
FROM users u
JOIN order_counts oc ON u.id = oc.user_idFix it once, format it always
Most of these problems share a root cause: SQL is written fast, under deadline, and not reread. The solution isn't a style guide that lives in a Confluence page nobody checks — it's running your queries through a SQL formatter before committing them. That handles indentation, keyword case, and clause structure automatically. For reviewing what changed between two versions of a query without formatting noise getting in the way, a text diff on the formatted output shows only the meaningful changes. And if the query's output lands as JSON — which it does more often than you'd think, once APIs get involved — keep a JSON formatter nearby so the results are as readable as the query that produced them.
Readable SQL isn't a luxury. It's the difference between a query that can be safely modified and one that's quietly load-bearing and terrifying to touch.
← All articles