Practical habits for readable, maintainable SQL queries

Readable SQL is operational documentation as much as code. A developer should be able to open a query, understand the business question it answers, and predict what it returns without tracing every expression by hand. Clear formatting, meaningful names, and deliberate joins make that possible while reducing the risk of expensive mistakes during incidents.

The opposite is familiar: a wall of uppercase keywords, unexplained aliases, nested subqueries, and filters added in whatever order seemed quickest. Such code can work perfectly until a requirement changes. Then the original author and the person on call are left reconstructing the logic under pressure.

Australian teams often work across Sydney, Melbourne, Brisbane, and Perth, so shared queries must survive different time zones and hybrid schedules. A stand-up may start before a colleague finishes a train commute, while production data is refreshed overnight for Australian trading hours. The SQL needs to communicate its assumptions rather than depend on who happened to write it.

The following practices apply to ad hoc analysis, application queries, views, and stored procedures. They work particularly well with SQL Server, but the principles transfer to most relational databases. The aim is not to make a query look clever; it is to make its purpose, row grain, and behaviour obvious.

Begin with the question, not the table

First, define the unit the query should return. “One row per customer” and “one row per order” lead to different joins, aggregations, and tests. If that grain is unclear, even correct SQL can produce plausible-looking duplicates or misleading totals. A short comment beside the query can state the intended grain and the business event it represents.

Use names to describe each stage. A common expression can turn a long pipeline into a readable sequence, especially when the logic includes filtering, grouping, and joining. Its name should answer “what is this collection?” rather than repeat its SQL keywords.

WITH order_totals AS (
    SELECT
        oi.order_id,
        SUM(oi.quantity * oi.unit_price) AS order_total
    FROM sales.order_items AS oi
    GROUP BY oi.order_id
)
SELECT
    o.order_id,
    o.customer_id,
    ot.order_total
FROM sales.orders AS o
JOIN order_totals AS ot
    ON ot.order_id = o.order_id
WHERE o.status = 'completed';

A common table expression does not guarantee good design, but it gives the query a visible narrative: calculate an amount for each order, then join it to the order. Keep each stage focused, and resist nesting several unrelated transformations inside one derived table.

Make names and columns tell the story

Choose aliases that reflect business concepts rather than arbitrary letters. customer_id, invoice_total, and sydney_warehouse tell a reader much more than c, amt, and w. Alias every table when more than one is present, and use the same alias consistently from the FROM clause through the select list and conditions.

Select the columns the query needs instead of using SELECT *. An explicit list is easier to review, protects the query from unexpected schema changes, and avoids sending large or sensitive fields across the network. It also makes the output contract visible to application developers and analysts.

SELECT
    o.order_id,
    o.customer_id,
    SUM(oi.quantity * oi.unit_price) AS invoice_total
FROM sales.orders AS o
JOIN sales.order_items AS oi
    ON oi.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY
    o.order_id,
    o.customer_id;

Use a consistent formatting convention across a file or repository: indent continuation lines, place keywords in a predictable case, and put major clauses on separate lines. These details reduce visual noise. Schema-qualified names such as sales.orders also prevent accidental ambiguity when similarly named tables exist in multiple schemas.

Build filters that are easy to inspect

Always parameterise values that come from users, applications, or scheduled jobs. It improves safety, allows plans to be reused, and separates query logic from changing input. It also makes the intended behaviour visible to reviewers.

DECLARE @from_date datetime2(3) = '2025-04-01T00:00:00';
DECLARE @to_date datetime2(3) = '2025-04-30T00:00:00';

SELECT
    o.order_id,
    o.customer_id,
    o.order_total
FROM sales.orders AS o
WHERE o.created_at >= @from_date
  AND o.created_at < @to_date
  AND o.status = @status;

Group related conditions with parentheses, especially when combining AND and OR. Keep the main path of the query easy to scan, and give a complex predicate a name in a common table expression if an inline condition becomes hard to explain. Comments should explain a business rule, not narrate the obvious.

Watch for functions or implicit conversions wrapped around indexed columns. Applying YEAR(created_at) to a date column can prevent an efficient range seek, while a leading wildcard in LIKE '%invoice%' requires extra scanning. Store values in a form that supports the intended comparison. When a filter is optional, separate strategies may be clearer and more efficient than a universal nullable-parameter pattern.

Keep joins explicit and intentional

Every join should state the relationship and preserve the intended row grain. Use INNER JOIN when unmatched records should be excluded, LEFT JOIN when they should remain, and FULL JOIN only when both sides genuinely need comparison. Never rely on an unqualified column name to identify the join condition.

FROM sales.orders AS o
LEFT JOIN sales.shipments AS s
    ON s.order_id = o.order_id
   AND s.status = 'dispatched';

The extra condition in this example belongs to the shipment relationship. Leaving it in a later WHERE clause would silently convert the outer join into an inner join by removing orders without a dispatched shipment. Put each predicate beside the relationship it constrains, and check what happens to unmatched rows.

Before joining large tables, ask what one row represents on each side. Joining orders to line items and then to payments can multiply totals several times. Pre-aggregate at the finest grain or use EXISTS when only presence matters. The second form avoids returning duplicate order rows:

SELECT
    c.customer_id,
    c.customer_name
FROM crm.customers AS c
WHERE EXISTS (
    SELECT 1
    FROM sales.orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.status = 'completed'
);

Treat NULL and aggregation as design decisions

NULL represents an unknown or absent value, not zero and not an empty string. Comparisons involving it require three-valued logic, so column = NULL does not test for null. Use IS NULL or IS NOT NULL, and decide explicitly how missing data should affect each calculation.

Aggregation also changes the grain of a result. COUNT(*) counts rows, while COUNT(column) skips null values; SUM ignores nulls but returns null when no non-null values exist. If zero is the required business result, make that policy visible with COALESCE or ISNULL. Test it with records containing nulls rather than assuming a populated sample covers it.

Pre-aggregating before a one-to-many join prevents accidental multiplication. This version calculates each order’s total before attaching customer details:

WITH order_totals AS (
    SELECT
        oi.order_id,
        SUM(oi.quantity * oi.unit_price) AS order_total
    FROM sales.order_items AS oi
    GROUP BY oi.order_id
)
SELECT
    o.order_id,
    c.customer_name,
    ot.order_total
FROM sales.orders AS o
JOIN order_totals AS ot
    ON ot.order_id = o.order_id
JOIN crm.customers AS c
    ON c.customer_id = o.customer_id;

Make dates predictable for Australian teams

Use half-open date ranges: greater than or equal to the start, and less than the end. This pattern represents complete calendar periods cleanly and avoids missing rows that share the final timestamp. It works well for daily, weekly, and monthly reports generated for Australian operating calendars.

WHERE o.created_at >= @from_utc
  AND o.created_at < @to_utc

Store event timestamps in UTC and convert only at the presentation or reporting boundary. Sydney and Melbourne observe daylight saving, while Brisbane does not; relying on the server’s local time can shift report boundaries twice a year. An explicit offset or UTC conversion keeps the query stable when daylight saving rules change.

Australian reporting adds familiar business boundaries. Many organisations align their financial year with 1 July to 30 June, while GST is calculated at 10% on taxable supplies. Queries used by Australian retailers, banks, and logistics businesses therefore need clear treatment of financial periods, tax-inclusive amounts, and local invoice dates. Australian forms may show DD/MM/YYYY, but ISO YYYY-MM-DD remains safer in database exchange and logs.

Review queries like production code

Treat formatting, naming, and decomposition as part of correctness. Put SQL under source control, review changes with the same care as application code, and keep schema changes in versioned migrations. For repeated logic, a view or stored procedure can centralise the definition, but it should still have a clear interface and an owner who understands its assumptions.

Test with representative data and edge cases. Include duplicate relationships, nulls, empty result sets, boundary timestamps, and enough volume to reveal an accidental cross join. Unit tests around a view or stored procedure can assert both the row grain and key totals. A query that passes only a small demonstration dataset may still fail once it meets real Australian customer and transaction patterns.

Protect sensitive data throughout the query lifecycle. Where the Privacy Act 1988 and the Australian Privacy Principles apply, data minimisation supports the principle that personal information should not be collected, used, or disclosed beyond what the stated purpose requires. APP 11 also expects reasonable security controls. Select only necessary fields, restrict access through database permissions, parameterise inputs, and ensure query logs do not expose credentials or personal details.

Monitor important queries after release. Execution plans, SQL Server Query Store, and basic timing metrics can show when a data-growth assumption no longer holds. Record the reason for expensive constructs rather than adding undocumented optimiser hints, and revisit them when usage changes. Readability makes those future investigations faster because the original intent is still visible.


Treat readability as a quality check with a concrete payoff: fewer defects during changes, quicker reviews, and calmer incident response. Apply the same pass whether the query serves a dashboard, a .NET application, or a one-off investigation for the finance team.

Bookmark this guide, pick one production query to review this week, and share the cleaned version with your team. Follow Kilt and Code for practical articles on SQL Server, .NET, testing, and building maintainable software.