Chapter 04 · Reading Data with SELECT
Readable Query Formatting and Naming Conventions
Make SQL easy to inspect, test, review, and modify by treating formatting and naming as part of query correctness.
Learning outcomes
SQL is executable documentation. Formatting does not usually change the statement’s meaning, but it strongly affects whether people can verify that meaning. A consistent style exposes missing predicates, ambiguous sources, accidental columns, and unsafe edits.
Format SELECT statements so clauses, expressions, and relationships are visually clear.
Choose table and column aliases that communicate role without excessive abbreviation.
Use comments and identifier conventions without creating hidden dependencies.
Refactor compressed or inconsistent SQL into a reviewable team style.
Readable SQL reduces reasoning cost
select s.sale_id,p.product_name,s.quantity*p.unit_price*(1-s.discount_rate) net from sale s join product p on p.product_id=s.product_id;SELECT s.sale_id, p.product_name, s.quantity * p.unit_price * (1 - s.discount_rate) AS net_amountFROM sale AS sJOIN product AS p ON p.product_id = s.product_id;The second form reveals the output list, source relationship, calculation grouping, and result alias. A reviewer can inspect one concern at a time.
A practical formatting baseline
| Element | Recommended baseline |
|---|---|
| Major clauses | Place SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY on separate lines |
| Select-list items | One expression per line when the list is nontrivial |
| Indentation | Use one consistent width; this academy uses four spaces for continuation and two for join predicates |
| Keywords | Use a consistent case; examples use uppercase |
| Commas | Choose a team convention and apply it consistently; examples use trailing commas between items |
| Semicolons | Terminate statements, especially in scripts and migration files |
| Parentheses | Use them to communicate intended grouping even when precedence is known |
| Blank lines | Separate logical statements or major CTE blocks, not every clause |
There is no single universal visual style. Consistency and clarity matter more than arguing over uppercase versus lowercase keywords.
One select-list expression per line
SELECT s.sale_id, s.sold_at, p.sku, p.product_name, s.quantity, p.unit_price, s.quantity * p.unit_price AS gross_amountFROM sale AS sJOIN product AS p ON p.product_id = s.product_id;This layout makes additions, removals, aliases, and code-review diffs easier to understand.
Alias names should communicate role
Short conventional alias
Useful when one customer source is present and the query is compact.
Role alias
Useful when the same entity appears in several roles, such as buyer and seller.
Descriptive alias
Useful for derived tables or CTEs whose role is more important than physical source.
Opaque alias
Avoid unless the scope is extremely small and conventional mathematics is clearer.
SELECT buyer.customer_id, buyer.full_name AS buyer_nameFROM customer AS buyer;Single-letter aliases are not automatically bad. They become bad when the reader cannot map them to a source or role.
Qualify columns in multi-source queries
Qualification prevents ambiguity and documents provenance.
SELECT s.sale_id, c.full_name AS customer_name, p.product_name, s.quantityFROM sale AS sJOIN customer AS c ON c.customer_id = s.customer_idJOIN product AS p ON p.product_id = s.product_id;Even if a column name is currently unique across sources, qualification can protect readability when schemas evolve.
Identifier naming conventions
| Convention | Benefit | Caution |
|---|---|---|
snake_case | Portable and readable in many SQL tools | Apply consistently |
| Singular table names | Rows read naturally as one entity occurrence | Plural is also valid if the team is consistent |
| Explicit key suffixes | customer_id communicates reference target | Do not encode changing data types in names |
| Unit suffixes | duration_seconds prevents ambiguity | Keep units aligned with stored meaning |
| Boolean-style names | is_active, has_access read as predicates | Some dialects lack a native Boolean type |
| Avoid reserved words | Reduces quoting and portability problems | Check target engines and tooling |
Names are long-lived interfaces. Prefer precise domain language over temporary UI labels.
Quoted identifiers create friction
-- Legal in many engines, but awkward and case-sensitive in some.SELECT "Order", "Customer Name"FROM "Sales Data";SELECT sale_id, customer_nameFROM sales_data;Quoted identifiers are necessary in some legacy or integration contexts, but ordinary lower-case names with letters, digits, and underscores are easier to move across tools and engines.
Comments should explain why
-- The export uses public SKU rather than internal product_id-- because downstream systems do not share our surrogate keys.SELECT p.sku, p.product_name, p.unit_priceFROM product AS p;Comments that merely restate syntax become stale. Explain business assumptions, unusual compatibility choices, expected invariants, or deliberate performance tradeoffs.
| Weak comment | Stronger comment |
|---|---|
| “Select products” | “Use SKU as the cross-system identifier” |
| “Join customer” | “Inner join is intentional: orphan sales are forbidden by the foreign key” |
| “Calculate total” | “Discount rate is stored as a fraction from 0 to 1” |
Keep related expressions aligned—but not artificially
SELECT p.sku AS product_code, p.product_name AS product_name, p.unit_price AS unit_priceFROM product AS p;Light alignment can improve scanning in short blocks. Excessive spaces create noisy diffs when names change. Use formatters and team rules consistently.
Refactoring lab
Refactor this statement without changing its result:
select s.sale_id,c.full_name,p.product_name,s.quantity,p.unit_price,s.quantity*p.unit_price*(1-s.discount_rate) total from sale s join customer c on c.customer_id=s.customer_id join product p on p.product_id=s.product_id;A production-ready version:
SELECT s.sale_id, c.full_name AS customer_name, p.product_name, s.quantity, p.unit_price, s.quantity * p.unit_price * (1 - s.discount_rate) AS net_amountFROM sale AS sJOIN customer AS c ON c.customer_id = s.customer_idJOIN product AS p ON p.product_id = s.product_id;Run both versions against the same database and compare rows and column values. Formatting changes should not silently change semantics.
Review SQL by semantic layers
A structured review checks meaning layer by layer instead of reading the statement as one visual block.
Query review questions
- Can the reviewer identify every output column and its source?
- Are aliases meaningful and consistent?
- Are calculations grouped and named clearly?
- Are joins and predicates visually separated?
- Does the query rely on quoted or reserved identifiers unnecessarily?
- Do comments explain assumptions rather than restate syntax?
- Is the result contract stable for its consumer?
- Has the formatted version been tested against representative data?
Common style failures
Formatting by personal preference in every file
Team consistency is more valuable than individually perfect aesthetics. Automate the accepted style where practical.
Renaming columns only to shorten them
Aliases should clarify the result, not hide domain meaning.
Using comments instead of structure
A comment cannot rescue an ambiguous join or a forty-expression single line. Format and decompose the SQL first.
Changing style and logic together
Large mixed diffs are hard to review. Separate semantic changes from mechanical formatting when possible.
Chapter 4 summary
You can now construct clear read-only query results:
SELECTdefines output expressions andFROMdefines sources;- aliases create query-local source names and stable output labels;
- explicit projections protect result contracts better than
SELECT *; DISTINCTremoves equal output rows only when uniqueness is the actual requirement;- expressions and literals create computed columns without changing stored data;
- consistent formatting and naming make SQL easier to verify and maintain.
Chapter 5 adds predicates, logical operators, pattern matching, sorting, and result limiting.