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 having 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 that everybody already follows.
The previous lesson in this module covered "How Do You Answer a Multi-Table Business Question End to End?". The next lesson in this module is about "What Differs Between MySQL, PostgreSQL and SQL Server?".
What query conventions keep team SQL maintainable?
Here is the same question written twice. The first version works, and 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.6And here is 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.6Both versions return the same seven rows, the same numbers, and the 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.
Here is the same answer a third time, in the shape a growing codebase wants. The logic is named once, and the final query is reduced to a sentence about the 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.6The 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. The style rules above are the reason that copy is not needed in the first place.
Which conventions actually matter?
- You write the 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 the JOIN forces the join condition to appear where a reader expects it.
- You alias every table, and you give the alias a meaningful name. The s, the r and the rg are fine. The t1, the t2 and the t3 are not. The alias is the vocabulary of the query.
- You always name the columns that you select. Never use the
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. - You alias the output columns to readable names. The
round(sum(s.quantity*s.unit_price*(1-s.discount)),2)is not a column name. It is an expression that happens to sit in the select list. - You uppercase the keywords and you lowercase the identifiers. This convention carries the least content of them all, and it is the one that makes a hundred-line query skimmable.
- You put one clause per line, and you put the FROM tables on their own lines. The formatting is what makes a diff readable, and a readable diff is how a review catches a mistake.
- You group by the key columns and not by their labels. Grouping by
rg.region_namemerges the two regions that happen to share a name. Grouping byrg.region_id, rg.region_namedoes not.
What about the naming?
The naming is where the conventions pay for themselves the most, because names outlive the query that created them.
Three rules cover most cases.
Name a thing after what it is, not after how it was computed. Say revenue and not sum_qty_price, because the calculation will change and the meaning will not.
Use the same word for the same concept in every query and every dashboard, so that "region" always means the column named region_name, and never the reg in one query and the region in another.
Give the CTEs the same discipline. monthly and targets are better than cte1 and cte2, because the name of the CTE 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?
Use consistent indentation, one join condition per line, and the clauses in the order the engine evaluates them. That order is 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 are worth adding.
Comment the non-obvious and 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 that is 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 the revenue, the customer names, and an unrelated count in the same result set has no single purpose, no single owner, and no single test.
Quick reference
- You write the JOIN and never a comma-separated FROM list, and you always alias the tables.
- You select the named columns with readable aliases, and you never use the
SELECT *in application SQL. - You group by the key columns rather than by the labels, so that identically named rows stay separate.
- You order the clauses the way the engine evaluates them, and you put each join on its own line.
- You name things after what they mean, and you use the same word for the same concept everywhere.
- One query, one question, one owner, one test.
Style drill: you take the messiest query you have written this month and you rewrite it in the house style. Then you count the lines you added. The added lines are the ones that will save somebody an hour when the query has to change.