Chapter 26 · advanced · 7 min
Writing clean, maintainable SQL
Every technique so far has been about getting the right answer. This closing chapter is about something equally real in interviews and on the job: writing SQL a teammate, or you, in six months, can actually follow. The two queries below return the identical result. One of them you'd want to inherit; the other you wouldn't.
The dataset
A small `customers` / `orders` pair, just enough to compare a messy query against a clean rewrite.
Before: it works, but it's a puzzle
This finds customers with at least one above-average order. It's correct, but SELECT * hides which columns actually matter, and the nested subquery makes you hold the whole logic in your head at once before it makes sense.
Same answer, harder to follow
After: named steps, explicit columns
A CTE gives the "average order" concept a name, avg_order, so the outer query reads as a sentence: find customers whose order exceeded avg_order. Explicit columns (name, city) also tell the reader exactly what the query is for, instead of making them guess from the schema:
| name | city |
|---|---|
| Aisyah | Kuala Lumpur |
| Chong | Johor Bahru |
Same result, named and explicit
Why this is worth the extra lines
This isn't just a style preference. In a real job, queries get inherited, copy-pasted into new reports, and debugged by people who weren't there when they were written. A query that takes 30 seconds to understand costs 30 seconds every single time someone touches it; across a team, over years, that adds up to a real amount of engineering time. "Correct" and "maintainable" are different bars, and interviewers who've worked on a real data team will often notice which one you're aiming for.
Four habits worth keeping
- Avoid
SELECT *outside of quick exploration; list the columns you actually need - Prefer a named CTE over a second (or third) level of nested subquery
- Give tables short, meaningful aliases (
cfor customers,ofor orders), and use them everywhere, not just where required - Comment the why, not the what; a one-line note above a business-rule threshold (like "orders under RM 20 are excluded as test transactions") saves the next reader from having to reverse-engineer it
If you've used Excel or Google Sheets
The spreadsheet equivalent of this habit is naming a cell or range (Formulas → Define Name) instead of referencing raw cell coordinates like $B$14 everywhere. A formula that says =C2 > avg_order is exactly as much clearer than =C2 > $B$14 as the CTE version is clearer than the nested-subquery version above. The underlying principle, give a meaningful name to anything you reference more than once, is the same in both tools.
Ready to put it all together? Viu's three-table join question below rewards exactly this style: name your CTEs, alias your tables, select only what you need.
That's the full tutorial, from your very first SELECT to the habits that separate a working query from a good one. The question bank is where all of this gets tested for real: every concept here has matching practice questions waiting.