SQL Best Practices

Best practices are habits that make SQL correct, secure, fast, readable, and maintainable across teams and years.

Concept

Best practices are habits that make SQL correct, secure, fast, readable, and maintainable across teams and years.

Why It Matters

Databases outlive applications. Consistent naming, constraints, and readable queries reduce defects and make onboarding, reviews, and performance tuning far easier.

Syntax

A readable query layout:

-- Purpose: monthly revenue per country, excluding cancelled orders.
-- Grain:   one row per (country, month).
WITH monthly AS (
    SELECT c.country,
           DATE_TRUNC('month', o.order_date)::DATE AS month,
           SUM(o.total_amount)                      AS revenue
    FROM orders AS o
    JOIN customers AS c
      ON c.customer_id = o.customer_id
    WHERE o.status <> 'CANCELLED'
    GROUP BY c.country, DATE_TRUNC('month', o.order_date)
)
SELECT country,
       month,
       revenue
FROM monthly
ORDER BY country, month;

Example

Meaningful table names order_items, diagnostic_dtc tbl1, data, oi_tmp_final2 Meaningful column names

order_date, odometer_km, is_active dt, val, flag, x1 Consistent naming conventions

snake_case, one plural/singular rule, _id, _at, is_ Mixed CamelCase, snake_case, quoted identifiers Avoid SELECT * List required columns

SELECT * in application code and views Explicit columns in INSERT INSERT INTO t (a, b) VALUES (...) INSERT INTO t VALUES (...) PK, FK, UNIQUE, NOT NULL, CHECK Integrity only in application code

Group related changes; handle errors with ROLLBACK Multi-step updates in autocommit mode Placeholders / prepared statements String concatenation of user input

Index FKs and frequent filters/sorts; verify with EXPLAIN Indexing every column; no indexes at all Avoid unnecessary joins

Join only tables whose columns or filters you need Joining "just in case" (fan-out, cost) Avoid unnecessary nesting CTEs, joins, window functions Four-level nested subqueries

CHECK constraints, types, input validation, data-quality queries Free-text statuses, dates as strings Document complex queries Comment purpose, grain, assumptions Unexplained magic numbers Check plans for important/slow queries Guessing about performance Keep queries readable

One clause per line, aligned JOINs, uppercase keywords, table aliases Single-line 400-character queries Additional practices:

Sargable predicates: never wrap indexed columns in functions in WHERE.

Half-open date ranges: >= start AND < next_start.

Deterministic ordering: add unique tie-breakers to ORDER BY, especially with pagination and ROW_NUMBER.

Explicit JOIN syntax instead of comma joins.

Qualify columns with aliases in multi-table queries.

Store money as DECIMAL and timestamps with time zone/UTC.

Least privilege for every account.

Schema changes via migrations in version control, reviewed like code.

Test SQL: expected-output checks on small datasets, pgTAP/tSQLt/utPLSQL for database code.

Batch large changes (chunks of thousands of rows) to limit locks and log growth.

Backups tested regularly with defined RPO/RTO.

Monitor: slow query logs, index usage, table growth, replication lag.

SQL style checklist:

[ ] Keywords UPPERCASE, identifiers lowercase snake_case
[ ] One selected column per line for long lists
[ ] Each JOIN on its own line with its ON condition
[ ] Meaningful short aliases (o for orders, c for customers)
[ ] Comments state purpose and grain
[ ] No SELECT * in production code
[ ] Consistent indentation (4 spaces)
[ ] Semicolon at the end of every statement

Expected Result

Applying these practices yields queries that reviewers understand in seconds, run efficiently, and remain correct as data and schemas evolve.

Common Mistakes

Treating SQL as "not real code" (no reviews, no tests, no version control).

Premature denormalization or over-indexing.

Copy-pasting queries between dialects without adapting syntax.

Best Practices

The table above *is* the list; adopt a team style guide and a formatter to enforce it automatically.

Interview Relevance

Medium: "What SQL best practices do you follow?", "Why avoid SELECT *?", "How do you make SQL maintainable?".

Practice Questions

Rewrite a query of your own using the readable layout above.

List five constraints you would add to a table that currently has none.

Explain two situations where SELECT * causes a production bug.

Write a team naming convention for tables, columns, indexes, and constraints.