Two queries that return the same rows are not equally good. One may be shorter, one may be faster, and one may be the version a colleague can still understand next year. Team SQL is not about a house style for its own sake — it is about making the next change safe, and the cheapest way to do that is a set of conventions everybody already follows.
What query conventions keep team SQL maintainable?
Here is the same question written twice. The first version works — it is the shape most people write when they are concentrating on getting the answer:
select rg.region_name,r.rep_name,round(sum(s.quantity*s.unit_price*(1-s.discount)),2) from sales s,reps r,regions rg where s.rep_id=r.rep_id and r.region_id=rg.region_id and s.sale_date>='2025-03-01' group by rg.region_name,r.rep_name order by 3 desc;
region_name rep_name round(sum(s.quantity*s.unit_price*(1-s.discount)),2)
----------- ------------- ----------------------------------------------------
North Bruno Kessler 3273.5
East Greta Vogel 2955.0
North Ada Whitfield 2918.9
West Elif Demir 2627.25
South Carla Nunes 2503.5
West Farid Haddad 2417.0
South Dev Patel 2175.6
And the version a team would merge:
SELECT rg.region_name AS region,
r.rep_name AS rep,
ROUND(SUM(s.quantity * s.unit_price * (1 - s.discount)), 2) AS revenue
FROM sales s
JOIN reps r ON r.rep_id = s.rep_id
JOIN regions rg ON rg.region_id = r.region_id
WHERE s.sale_date >= '2025-03-01'
GROUP BY rg.region_id, rg.region_name, r.rep_id, r.rep_name
ORDER BY revenue DESC;
region rep revenue
------ ------------- -------
North Bruno Kessler 3273.5
East Greta Vogel 2955.0
North Ada Whitfield 2918.9
West Elif Demir 2627.25
South Carla Nunes 2503.5
West Farid Haddad 2417.0
South Dev Patel 2175.6
Same seven rows, same numbers, same order. But the second one can be changed safely and the first one cannot. The differences are worth counting, because each one is a rule you can put in a style guide.
And the same answer a third time, in the shape a growing codebase wants — the logic named once, and the final query reduced to a sentence about named things:
WITH rep_revenue AS (
SELECT rg.region_id,
rg.region_name AS region,
r.rep_id,
r.rep_name AS rep,
SUM(s.quantity * s.unit_price * (1 - s.discount)) AS revenue
FROM sales s
JOIN reps r ON r.rep_id = s.rep_id
JOIN regions rg ON rg.region_id = r.region_id
WHERE s.sale_date >= '2025-03-01'
GROUP BY rg.region_id, rg.region_name, r.rep_id, r.rep_name
)
SELECT region, rep, ROUND(revenue, 2) AS revenue
FROM rep_revenue
ORDER BY revenue DESC;
region rep revenue
------ ------------- -------
North Bruno Kessler 3273.5
East Greta Vogel 2955.0
North Ada Whitfield 2918.9
West Elif Demir 2627.25
South Carla Nunes 2503.5
West Farid Haddad 2417.0
South Dev Patel 2175.6
The third version is the one that scales. Once the calculation lives in a named CTE, a second report can reuse it without copying the arithmetic — and the style rules above are the reason the copy is not needed in the first place.
Which conventions actually matter?
- Write JOIN instead of a comma-separated FROM list. The comma form is an implicit cross join that is easy to miss and hard to read. Writing JOIN forces the join condition to appear where a reader expects it.
- Alias every table, and give the alias a meaningful name.
s,randrgare fine;t1,t2,t3are not. The alias is the vocabulary of the query. - Always name the columns you select. Never
SELECT *in application or reporting SQL: the column order can change under you, the query transfers more data than it needs, and a covering index can no longer answer it. - Alias the output columns to readable names.
round(sum(s.quantity*s.unit_price*(1-s.discount)),2)is not a column name; it is an expression that happens to be in the select list. - Uppercase the keywords, lowercase the identifiers. It is the convention with the least content, and it is the one that makes a hundred-line query skimmable.
- One clause per line, and put the FROM tables on their own lines. Formatting is what makes a diff readable, and a readable diff is how a review catches a mistake.
- Group by the key columns, not by their labels. Grouping by
rg.region_namemerges two regions that happen to share a name; grouping byrg.region_id, rg.region_namedoes not.
What about naming?
Naming is where conventions pay for themselves most, because names outlive the query that created them. Three rules cover most cases. Name the thing after what it is, not after how it was computed — revenue rather than sum_qty_price, because the calculation will change and the meaning will not. Keep the same word for the same concept across every query and every dashboard, so that "region" always means the column named region_name and never reg in one query and region in another. And give CTEs the same discipline: monthly and targets are better than cte1 and cte2, because the CTE's name is the only description its readers get.
The same applies to the tables themselves. A well-named schema is a documentation system that cannot go out of date: region_targets tells you what the table is for, and sales tells you the grain. Renaming a column to amt because it is shorter costs every future reader a lookup.
What formatting survives a review?
Consistent indentation, one join condition per line, and the clauses in the order the engine evaluates them: FROM, WHERE, GROUP BY, HAVING, WINDOW, SELECT, ORDER BY, LIMIT. That order is not a coincidence — it is the sequence module 18 taught, and writing it in that order means the reader's eye travels the way the data does.
Three more practical conventions. Comment the non-obvious, not the obvious. A comment that says -- join to regions adds nothing; a comment that says -- target is set monthly, so compare on the month key not the quarter saves somebody an argument later. Parameterise the literals. A date written as '2025-03-01' in four places is four places to change; a named constant or a bound parameter is one. And keep a query that answers one question. A query that returns revenue, customer names and an unrelated count in the same result set has no single purpose, no single owner and no single test.
Quick reference
- Write JOIN, never a comma-separated FROM list, and always alias the tables.
- Select named columns with readable aliases; never SELECT * in application SQL.
- Group by key columns rather than by labels, so identically named rows stay separate.
- Order the clauses as the engine evaluates them and put each join on its own line.
- Name things after what they mean, and use the same word for the same concept everywhere.
- One query, one question, one owner, one test.
Style drill: take the messiest query you have written this month and rewrite it in the house style — then count the lines you added. The added lines are the ones that will save somebody an hour when the query has to change.