SQL Best Practices: Readable and Maintainable Queries

Lesson 10.1 · Reading time: ~8 min

SQL best practices are the formatting and naming habits that make a query easy to read, review and change. In this lesson you'll learn how to lay out clauses and columns, how to name tables, columns and aliases, when comments help, which habits prevent bugs, and how to turn a one-line query into code your team can maintain.

In the previous module you worked with views and temporary tables, and your queries grew to many joins and several steps. At that size, code quality matters as much as correctness: SQL is read far more often than it is written, by teammates during review and by you a few weeks later.

Well-structured code makes bugs visible, speeds up review, and makes queries easier to extend. None of these rules change what the database does; they change how quickly a person understands the query.

Why Code Style Matters

A query of 5–10 lines is clear at a glance. A report with many JOIN clauses, subqueries or CTEs is not, and a consistent style helps you:

• Find bugs faster: a wrong filter or a missing join condition stands out in clean code.
• Extend queries safely: a new column or condition has an obvious place.
• Work as a team: everyone reads code in the same layout, and reviews focus on logic instead of style.

Formatting and Indentation

SQL ignores whitespace and the case of keywords, so the layout is up to you. These conventions are the most widely used.

Uppercase Keywords

Write SQL keywords (SELECT, FROM, WHERE, JOIN, GROUP BY) in uppercase and names in lowercase. The structure of the query then stands out from the data it uses.

-- Hard to scan
select title, rental_rate from film where rating = 'G';

-- Easier to scan
SELECT title, rental_rate
FROM film
WHERE rating = 'G';

Result: both queries return the same 178 films; only the layout differs.

One Clause per Line

Start each main clause on a new line. When SELECT or GROUP BY lists many columns, put each one on its own line, indented:

SELECT
    customer_id,
    first_name,
    last_name,
    email
FROM customer
WHERE active = 1
ORDER BY last_name;

With one column per line, adding or removing a column changes one line, and the diff in code review shows exactly that line.

Naming Conventions

Lowercase and snake_case

The de facto standard is lowercase names with underscores between words: customer_orders, total_amount, not CustomerOrders or TotalAmount. Databases treat identifier case differently: PostgreSQL folds unquoted names to lowercase, and MySQL on Linux treats table names as case-sensitive. snake_case avoids these surprises.

Consistent Prefixes

Some teams mark object types with prefixes, such as v_ for views (v_active_customers) and tmp_ for temporary tables (tmp_monthly_report). Sakila doesn't use prefixes: its views are named customer_list, film_list, sales_by_film_category. Either way works; what matters is choosing one rule for the whole database.

SELECT category, total_sales
FROM sales_by_film_category
ORDER BY total_sales DESC
LIMIT 3;

Result: the three categories with the highest sales: Sports, Sci-Fi and Animation.

Clear Aliases

Table Aliases

Use short but meaningful aliases, such as the first letters of the table name. Aliases like t1, t2, a, b force the reader to look up what each one means:

-- Unclear: what are t1 and t2?
SELECT t1.first_name, t2.amount
FROM customer t1
JOIN payment t2 ON t1.customer_id = t2.customer_id;

-- Clear
SELECT c.first_name, p.amount
FROM customer c
JOIN payment p ON c.customer_id = p.customer_id;

Names for Calculated Columns

Give every aggregate and expression a name with AS. A report column called COUNT(*) is hard to use in an application or a spreadsheet:

SELECT
    c.name AS category_name,
    COUNT(*) AS film_count,
    AVG(f.replacement_cost) AS avg_replacement_cost
FROM film f
JOIN film_category fc ON f.film_id = fc.film_id
JOIN category c ON fc.category_id = c.category_id
GROUP BY c.name
ORDER BY film_count DESC;

Result: 16 categories with clear column names; Sports has the most films, 74.

Comments

Comments explain why the code does something when the reason isn't obvious from the code itself: a business rule, an unusual filter, a known data issue.

• Single-line comments (--) explain a filter or a formula.
• Block comments (/* ... */) describe the purpose of a script.

/*
  Report: customer spending in July 2005
  Used by: monthly marketing newsletter
*/
SELECT
    customer_id,
    SUM(amount) AS july_spent
FROM payment
WHERE payment_date >= '2005-07-01'  -- the whole month,
  AND payment_date < '2005-08-01'   -- including July 31 after midnight
GROUP BY customer_id
ORDER BY july_spent DESC;

Result: 599 customers with their spending in July 2005.

Don't comment what the code already says: -- select customer id above SELECT customer_id only adds noise, and outdated comments are worse than none.

Habits That Make Queries Safer

• List the columns you need instead of SELECT * in reports, views and application code. A new column in the table then can't change your result or slow it down.
• Use explicit JOIN ... ON instead of listing tables with commas in FROM. The join condition stays next to the table and is harder to forget.
• Prefer half-open date ranges (>= start AND < end) to BETWEEN for date-time columns, as in the example above.
• Agree on a style and automate it. Formatters and linters such as SQLFluff apply the rules for you, so reviews don't argue about spaces.

Practical Example: Cleaning Up a Messy Query

Before: everything on one line, no aliases for calculated columns:

select f.title,c.name,count(r.rental_id) from film f join film_category fc on f.film_id=fc.film_id join category c on fc.category_id=c.category_id join inventory i on f.film_id=i.film_id join rental r on i.inventory_id=r.inventory_id group by f.title,c.name having count(r.rental_id)>30 order by count(r.rental_id) desc;

After: one clause per line, aligned joins, named columns:

SELECT
    f.title,
    c.name AS category_name,
    COUNT(r.rental_id) AS rental_count
FROM film f
JOIN film_category fc ON f.film_id = fc.film_id
JOIN category c       ON fc.category_id = c.category_id
JOIN inventory i      ON f.film_id = i.film_id
JOIN rental r         ON i.inventory_id = r.inventory_id
GROUP BY f.title, c.name
HAVING COUNT(r.rental_id) > 30
ORDER BY rental_count DESC;

Result: both versions return the same 16 films rented more than 30 times, led by BUCKET BROTHERHOOD with 34 rentals.

In the second version, you see at a glance which tables are joined and how, what is counted, and how the result is filtered and sorted.

Key Takeaways:

• Write keywords in uppercase and names in lowercase snake_case.
• Start each clause on a new line and put long column lists one per line.
• Use meaningful table aliases and name every calculated column with AS.
• Comment why, not what: business rules and non-obvious filters.
• List columns instead of SELECT *, use explicit JOIN ... ON, and half-open date ranges.
• Agree on one style for the team and apply it with a formatter or linter.

Frequently Asked Questions

Do SQL keywords have to be uppercase?

No. Databases accept keywords in any case. Uppercase keywords are a convention that makes the structure of long queries easier to see; what matters most is that the whole team uses the same style.

What makes SQL code maintainable?

Consistent formatting, clear names and aliases, named calculated columns, and short comments where the logic isn't obvious. Such code is easier to review, change and debug, also by people who didn't write it.

Why should I avoid SELECT * in production queries?

SELECT * returns every column, including ones added to the table later. That can break applications that expect a fixed set of columns, transfer more data than needed and prevent the use of covering indexes. List the columns you need.

How do I clean up a messy SQL query?

Split it into clauses on separate lines (SELECT, FROM, JOIN, WHERE, GROUP BY, ORDER BY), indent the column lists, replace unclear aliases, name the calculated columns, and add a comment where the logic needs an explanation. Run the old and new versions to check that the results match.