Anyone can write SQL that returns a result. Writing SQL that stays correct, stays fast, and stays readable a year after you have forgotten why you wrote it is a discipline. This is the reference for that discipline: the practices that separate a query you can trust from one that merely runs.
Where our roundups of common database design mistakes and SQL code review best practices tell you what to avoid and how to catch it, this guide is the positive counterpart: a complete, prescriptive "how to do it right" playbook you can hand to a new hire or pin above your desk. Every section states a rule, explains why it exists, and shows a wrong-versus-right pair so the rule sticks. The examples are engine-neutral where possible; where a syntax is dialect-specific, the comment says so. Work through it once end to end, then keep it open while you write.
What this guide covers
- Naming conventions
- Formatting and readability
- Always name your columns
- Write sargable, set-based queries
- Use constraints for data integrity
- Choose the correct data types
- Handle NULLs deliberately
- Security: parameterize and least privilege
- Transactions: short, explicit, safe
- Indexing discipline
- Comments and reversible migrations
- Test your SQL and read the plan
- The best-practices checklist
Naming conventions that scale
Names are the interface to your schema. A consistent naming scheme means a developer can guess a column name correctly without opening the table, and that guessing rate is a real productivity multiplier across a team. The exact rules matter less than picking one set and applying it everywhere. The conventions below are the widely used defaults; adopt them or adopt your own, but write them down and never mix styles.
| Object | Convention | Example |
|---|---|---|
| Table | snake_case, singular or plural (pick one), lower case | customer or customers |
| Column | snake_case, no table prefix, spell it out | created_at, email_address |
| Primary key | id, or <table>_id if you prefer explicit | id / order_id |
| Foreign key | <referenced_table>_id | customer_id |
| Index | ix_<table>_<cols> (or ux_ for unique) | ix_orders_customer_id |
| Constraint | <type>_<table>_<detail> | fk_orders_customer, chk_orders_total_pos |
| Boolean column | positive predicate, is_ / has_ prefix | is_active, has_shipped |
Three rules earn their keep more than the rest. First, be consistent about singular versus plural table names and never switch mid-schema. Second, avoid reserved words and quoting: a column called order or user forces quotes forever, so prefer orders or account. Third, do not encode the type in the name (strName, tblCustomer) because the type will change and the name will lie.
-- Cryptic, prefixed, reserved words, mixed case
CREATE TABLE tblCust (
CustID INT,
fName VARCHAR(50),
"order" INT, -- reserved word, needs quotes forever
flag INT -- flag for what?
);-- Predictable, self-describing, no quoting needed
CREATE TABLE customers (
id BIGINT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
order_count INT NOT NULL DEFAULT 0,
is_active BOOLEAN NOT NULL DEFAULT TRUE
);Why it matters: naming is the cheapest form of documentation you will ever write, and the only one that is impossible to let go stale, because the name travels with the object. Get it right at CREATE TABLE time and every query afterward reads a little more like plain English.
Formatting and readability
SQL is read far more often than it is written, and a query you cannot read is a query whose bugs you cannot see. Formatting is not vanity; it is how the shape of a statement reveals its logic. Adopt a house style and, ideally, enforce it with an auto-formatter so the rules are settled by configuration rather than by argument.
- Keyword case is consistent. Upper case keywords (SELECT, FROM, WHERE) against lower case identifiers is the most common choice because it makes the clause structure pop.
- One column per line in the select list, and one condition per line in a multi-part WHERE. Diffs become one-liners and the eye scans vertically.
- Explicit JOIN ... ON syntax, never comma joins. Comma joins hide the join condition among the filters and invite accidental cross joins.
- Meaningful aliases. c for customers is fine; a, b, c for three unrelated tables is a puzzle.
select o.id,c.name,i.title from orders o,customers c,items i where o.cid=c.id and i.oid=o.id and o.status=1 and c.country='US';-- Active US orders with customer and line item
SELECT o.id AS order_id,
c.name AS customer_name,
i.title AS item_title
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN items i ON i.order_id = o.id
WHERE o.status = 1 -- 1 = active
AND c.country = 'US';The two statements are identical to the engine and worlds apart to a human. When you can see each join condition and each filter on its own line, a missing ON clause or a stray predicate is obvious. That readability is exactly what makes the review practices in our SQL code review guide possible.
Always name your columns; avoid SELECT *
SELECT * is perfect for exploring at the prompt and wrong in code that ships. Naming the columns you actually use is one of the highest-leverage habits in this whole guide, because it fixes four problems at once.
- Stability. Adding or reordering a column silently changes what * returns, which breaks positional reads and ordinal-based application code.
- Performance. You fetch and transfer columns nobody uses, including wide TEXT and BLOB data, wasting I/O and network.
- Covering indexes. An index that contains every selected column lets the engine answer from the index alone. SELECT * almost always forces a trip back to the table.
- Intent. The named list documents exactly what the query depends on.
-- Fetches every column, breaks if the table changes
SELECT * FROM users WHERE id = ?;-- Explicit list: stable, lean, index-friendly
SELECT id, email, display_name
FROM users
WHERE id = ?;The same rule applies to INSERT: always list the target columns rather than relying on positional order, so a new column with a default does not shift every value into the wrong slot.
-- Positional insert: a new column silently misaligns this
INSERT INTO orders VALUES (1, 42, '2026-07-04', 99.50);INSERT INTO orders (id, customer_id, ordered_at, total)
VALUES (1, 42, '2026-07-04', 99.50);Write sargable, set-based queries
Two ideas drive most of SQL performance at the query level: let the engine use indexes (sargability), and let the engine work on whole sets at once instead of looping row by row. Master both and you avoid the majority of self-inflicted slowness. For the schema-level side of speed, see our performance tuning reference.
Sargability: keep the column bare
A predicate is sargable (Search ARGument able) when the engine can seek an index for it. Wrapping the indexed column in a function, doing arithmetic on it, or leading a LIKE with a wildcard all defeat the index and force a full scan.
-- Function on the column: the index on created_at is unusable
WHERE YEAR(created_at) = 2026;
-- Arithmetic on the column defeats the index too
WHERE price * 1.2 > 100;
-- Leading wildcard: no seek possible
WHERE email LIKE '%@gmail.com';-- Range on the raw column keeps the index seekable
WHERE created_at >= '2026-01-01'
AND created_at < '2027-01-01';
-- Move the math to the constant side
WHERE price > 100 / 1.2;
-- Anchor the wildcard, or use full-text search instead
WHERE email LIKE 'admin%';Set-based over row-by-row
SQL is a set language. A cursor or an application loop that issues one statement per row (RBAR, row by agonizing row) throws away the optimizer's ability to plan the work in bulk. Whenever you see a loop building or mutating data, ask whether a single statement can express it.
-- Loop that updates one row at a time, thousands of round trips
DECLARE c CURSOR FOR SELECT id FROM users WHERE plan = 'pro';
-- open; fetch; UPDATE users SET credit = credit + 5 WHERE id = @id; loop; close-- One statement the optimizer can plan and parallelize
UPDATE users
SET credit = credit + 5
WHERE plan = 'pro';The same thinking eliminates the N+1 pattern, where application code runs one query for a list and then one more per item. Replace it with a single join or an IN batch.
-- One round trip instead of N+1
SELECT i.order_id, i.title, i.qty
FROM order_items i
JOIN orders o ON o.id = i.order_id
WHERE o.customer_id = ?;Watch out: a single giant UPDATE or DELETE over tens of millions of rows can hold locks and bloat the transaction log. Set-based is the default, but for very large mutations, chunk the work into batches by primary-key range and commit between batches. Set-based thinking and batched execution are not in conflict; the batches are still set-based, just bounded.
Use constraints for data integrity
The database is the last line of defense for your data, and it is the only one every path has to go through. Application checks can be bypassed by a bulk import, a second service, or a hotfix in a psql session; a constraint cannot. Push every invariant you can down into the schema so bad data is rejected at the source rather than discovered in a report.
| Constraint | Guarantees | Use it for |
|---|---|---|
| PRIMARY KEY | Row is uniquely identifiable, not NULL | Every table, always |
| FOREIGN KEY | Reference points at a real parent row | Every relationship |
| UNIQUE | No duplicate values in a column or set | Emails, slugs, natural keys |
| CHECK | Value satisfies a business rule | Non-negative amounts, valid enums |
| NOT NULL | Column always has a value | Anything not genuinely optional |
| DEFAULT | Sensible value when none is supplied | Timestamps, flags, counters |
-- Everything nullable, no keys, nothing enforced
CREATE TABLE orders (
id INT,
customer_id INT,
total DECIMAL(10,2),
status VARCHAR(20)
);
-- Nothing stops total = -50, an orphan customer_id, or a duplicate idCREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL
REFERENCES customers(id),
total DECIMAL(10,2) NOT NULL
CHECK (total >= 0),
status VARCHAR(20) NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending','paid','shipped','cancelled')),
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE (customer_id, created_at)
);Constraints are also the foundation of good database normalization: keys and foreign keys are what make a normalized model actually enforce the relationships it models. A schema without constraints is a spreadsheet with extra steps.
Choose the correct data types
The type you pick is a promise about the values a column can hold and how they behave in arithmetic, sorting, and comparison. The wrong type is a bug that lies dormant until the value that breaks it arrives. A few choices come up again and again.
- Money is never a float. Binary floating point cannot represent 0.10 exactly, so sums drift by a cent and reconciliations fail. Use DECIMAL/NUMERIC with explicit precision, or store integer minor units (cents).
- Dates and times get real temporal types. A date stored as text cannot be range-scanned, sorts wrong, and accepts "2026-13-40". Use DATE, TIMESTAMP, and prefer a timezone-aware type for anything crossing zones.
- Size strings to the domain. Do not default everything to VARCHAR(255) or TEXT. A country code is CHAR(2); an email has a sane max. Right-sizing documents intent and helps the planner estimate.
- Integers over strings for identifiers and enums where the set is fixed, and native BOOLEAN over a char(1) flag.
CREATE TABLE payments (
amount FLOAT, -- rounding errors in every sum
paid_on VARCHAR(30), -- 'yesterday'? '07/04/26'? unsortable
currency VARCHAR(255), -- 3 chars in a 255 box
is_settled VARCHAR(5) -- 'true','yes','Y','1'... pick one
);CREATE TABLE payments (
amount DECIMAL(12,2) NOT NULL, -- exact to the cent
paid_on TIMESTAMP NOT NULL, -- real temporal type
currency CHAR(3) NOT NULL, -- ISO 4217, fixed width
is_settled BOOLEAN NOT NULL DEFAULT FALSE
);Watch out: the classic float trap is invisible until it is not. SUM a million FLOAT amounts and the total can be off by several cents; a WHERE amount = 0.10 can match zero rows because the stored value is 0.09999999999999999. Money and comparisons for equality demand exact types. Choosing types well up front is one of the cheapest ways to avoid the design mistakes that are painful to migrate later.
Handle NULLs deliberately
NULL means "unknown," not "zero" and not "empty string," and SQL evaluates it with three-valued logic: a comparison can be true, false, or unknown. Rows that evaluate to unknown are dropped by WHERE. Almost every NULL bug is a place where the author forgot that third value exists.
-- Rows with status = NULL are silently excluded here
SELECT id FROM orders WHERE status <> 'shipped';
-- If the subquery returns a single NULL, this returns ZERO rows, always
SELECT id FROM users
WHERE id NOT IN (SELECT user_id FROM bans);
-- = NULL is never true; this matches nothing
SELECT id FROM orders WHERE shipped_at = NULL;-- Include the unknowns explicitly if that is the intent
SELECT id FROM orders
WHERE status <> 'shipped' OR status IS NULL;
-- NOT EXISTS is NULL-safe where NOT IN is not
SELECT u.id FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM bans b WHERE b.user_id = u.id);
-- Test for NULL with IS NULL, never = NULL
SELECT id FROM orders WHERE shipped_at IS NULL;Use COALESCE to substitute a default when NULL would poison a calculation, and remember that most aggregates ignore NULL, which is sometimes what you want and sometimes a trap.
-- Treat missing discount as zero; avg over present values only
SELECT order_id,
price - COALESCE(discount, 0) AS net,
AVG(rating) AS avg_rating -- NULL ratings ignored
FROM order_lines
GROUP BY order_id, price, discount;Decide, for every nullable column, what NULL means in each query that touches it. The best defense is fewer nullable columns in the first place: if a value is always required, make it NOT NULL with a DEFAULT and the whole class of bug disappears.
Security: parameterize and grant the minimum
The single most important security rule in all of SQL is that data never becomes code. User input goes into parameters that the driver binds separately; it never gets concatenated into the string that the database compiles. Do this everywhere and SQL injection, the most damaging common defect, simply cannot occur. Our SQL security guide covers the full threat model; this is the non-negotiable core.
// String building: one apostrophe and the query is the attacker's
sql = "SELECT id FROM users WHERE email = '" + email + "'";
-- The attacker sends this as the email value:
-- ' OR '1'='1' --// Parameterized: SQL text and values travel separately
sql = "SELECT id FROM users WHERE email = ?";
stmt.setString(1, email);The rule holds in every language and every framework. Identifiers that cannot be parameterized, such as a dynamic ORDER BY column or a table name, must be validated against an allow-list of known-safe values, never interpolated from input.
// Sort column comes straight from the query string: injectable
sql = "SELECT * FROM products ORDER BY " + sortCol;// Map the untrusted key to a fixed set of real column names
allowed = { "price": "price", "name": "name" };
col = allowed[sortCol] || "name";
sql = "SELECT id, name, price FROM products ORDER BY " + col;Least privilege
Parameterizing stops the injection; least privilege limits the damage of anything that slips through. The account your application connects with should hold exactly the rights it needs and nothing more, so a compromised query cannot drop tables or read another schema.
GRANT ALL PRIVILEGES ON *.* TO 'app'@'%';GRANT SELECT, INSERT, UPDATE, DELETE
ON shop.* TO 'app'@'10.0.%';Why it matters: parameterization and least privilege are defense in depth. The first makes the common attack impossible; the second makes the rare bypass survivable. Neither is optional, and neither replaces the other. See the full treatment in the SQL security reference.
Transactions: short, explicit, and safe
A transaction is how you keep multiple writes all-or-nothing. Two rules govern good transaction use: make the boundary explicit, and keep the transaction short. A transaction that stays open while your application waits on a network call or user input holds locks the whole time and becomes a scalability ceiling.
-- No transaction: a crash between these two leaves money missing
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;Inside a stored procedure, that atomicity needs an error handler that rolls back and re-raises rather than swallowing the failure and reporting a false success.
-- MySQL: roll back on any SQL exception, then re-signal
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;
START TRANSACTION;
-- ... the work that must be atomic ...
COMMIT;Keep only the writes that must be atomic inside the transaction. Do validation, external calls, and heavy computation before BEGIN. And because concurrent transactions can deadlock no matter how careful you are, any write path that might deadlock needs a short retry loop with backoff, and locks should be acquired in a consistent order across procedures to make deadlocks rarer.
Indexing discipline
Indexes are the single biggest lever for read performance, and every one of them is a tax on writes and storage. The discipline is to index deliberately: cover what you filter and join on, and resist the urge to index everything. For the full mechanics see our SQL indexes reference; the practices below are the rules of thumb.
- Index the columns you filter and join on in your hot queries, especially foreign keys, which are unindexed by default in some engines and turn joins into scans.
- Order composite index columns equality-first, then range: an index on (status, created_at) serves WHERE status = ? AND created_at > ?, but not a query that filters on created_at alone.
- Do not over-index. Each index slows every INSERT, UPDATE, and DELETE and consumes space. Redundant and unused indexes are pure cost. Drop indexes no query uses.
- Consider covering indexes that include the selected columns so the query is answered from the index alone.
-- One index per column, several never used, FK left unindexed
CREATE INDEX ix_o_status ON orders(status);
CREATE INDEX ix_o_total ON orders(total);
CREATE INDEX ix_o_created ON orders(created_at);
-- The real query filters status + date together and joins on customer_id-- One composite for the hot filter, one on the foreign key
CREATE INDEX ix_orders_status_created
ON orders(status, created_at);
CREATE INDEX ix_orders_customer_id
ON orders(customer_id);Let real query patterns and the plan drive index choices, not guesses. Add an index because a slow query needs it and the plan confirms it helps, then verify the write cost is acceptable.
Comments and reversible migrations
Code documents how; comments document why. SQL especially benefits, because a magic number or a subtle filter is invisible intent. Comment the non-obvious: a business rule encoded as a constant, a workaround for a known data quirk, the reason a query is shaped the way it is. Do not narrate the obvious.
SELECT id, total
FROM orders
WHERE status = 3 -- 3 = 'refunded', see order_status enum
AND total > 0 -- exclude zero-value test rows from 2024 import
AND created_at >= '2026-01-01';Version-controlled, reversible migrations
Every schema change is code and belongs in version control as a migration, applied in order, the same way in every environment. Two properties make a migration safe: it should be reversible or forward-safe, and re-running it must not blow up. Guards make re-runs idempotent; a down script makes a bad deploy recoverable.
-- Ad hoc, fails on the second run, no way back
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;
CREATE INDEX ix_users_last_login ON users(last_login);-- up.sql: idempotent, low-lock
ALTER TABLE users
ADD COLUMN IF NOT EXISTS last_login TIMESTAMP;
CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_users_last_login
ON users(last_login);
-- down.sql: a tested path back
DROP INDEX IF EXISTS ix_users_last_login;
ALTER TABLE users DROP COLUMN IF EXISTS last_login;Separate data backfills from schema DDL, prefer non-blocking operations on large tables, and dry-run every migration against a production-sized copy before it merges. Our design mistakes guide shows how skipping these steps turns a routine deploy into an outage.
Test your SQL and read the plan
SQL is code, and untested code is broken code you have not caught yet. Two habits close the loop: test the edges, and read the plan before you ship. Together they turn "it worked on my data" into evidence.
Test the edges, not just the happy path
The rows that break queries are the unusual ones. Build a small fixture that includes them and assert the result.
Empty set
Run the query against zero matching rows. Does an aggregate return NULL where the app expects 0? Wrap it in COALESCE.
NULLs present
Include rows with NULL in every nullable column the query touches, and confirm they are kept or dropped on purpose.
Duplicates and fan-out
Add a parent with several children and check that a join does not multiply your totals.
Boundaries
Test the exact dates and thresholds in your ranges. Off-by-one on a BETWEEN or a timezone is a classic silent error.
Read EXPLAIN before you ship
Never ship a nontrivial query without looking at its plan. EXPLAIN (and EXPLAIN ANALYZE where it is safe to actually run) turns a performance guess into a fact.
EXPLAIN ANALYZE
SELECT id, email FROM users WHERE email = 'x@example.com';What to look for, and what each finding is telling you to fix:
| Plan line | What to ask |
|---|---|
| Seq Scan / Full Table Scan on a big table | Missing index, or a non-sargable predicate? |
| Row estimate far from actual | Stale statistics; run ANALYZE. |
| Nested Loop over a large outer set | A hash/merge join or a batch would be better. |
| Sort spilling to disk | Can an index provide the order instead? |
| Filter dropping most rows after the scan | Move the predicate into an indexable form. |
Norm to set: treat the query plan the way you treat a test result. If a query touches a large table, its plan is part of the definition of done. This is exactly the habit our code review guide asks reviewers to insist on.
The best-practices checklist
Everything above condensed into a scannable list. Severity tells you whether a violation blocks the merge or is a follow-up. Print it, or bake it into your pull-request template.
| Practice | Why | Severity |
|---|---|---|
| User input is parameterized, never concatenated | Closes the top critical vulnerability class | Blocker |
| App account has least-privilege grants | Limits blast radius of any bug or breach | Blocker |
| Related writes share one short transaction | Prevents partial, data-losing states | Blocker |
| Keys and constraints enforce every invariant | The database is the last line of defense | High |
| Money uses DECIMAL; dates use temporal types | Avoids silent rounding and sort errors | High |
| NULLs handled with IS NULL / NOT EXISTS | Three-valued logic drops rows silently | High |
| Predicates are sargable (bare column) | Keeps indexes usable at scale | High |
| Set-based over cursors and N+1 loops | Orders-of-magnitude performance | High |
| Indexes cover hot filters and foreign keys | Turns scans into seeks | High |
| Migrations are reversible and idempotent | Re-runs and rollbacks stay safe | High |
| Columns named explicitly, no SELECT * | Stable, lean, index-friendly queries | Medium |
| Consistent naming and formatting | Readers can see the bug | Medium |
| Non-obvious logic is commented | Preserves intent for the next person | Medium |
| EXPLAIN reviewed; edge cases tested | Catches slow and wrong before production | Medium |
Where teams most often slip
Not all of these rules are broken equally often. The chart below is representative of where practice tends to lag intention, and it is a useful guide to where a new team should focus its conventions first.
The takeaway is not that injection matters least; it is rare precisely because most teams already parameterize, and it is catastrophic when missed. The volume lives in indexing, query shape, and NULLs, so those are where a written standard and a linter pay off fastest. Best practices are not a one-time cleanup; they are a habit you build by writing them down, reviewing against them, and testing every change. Work them into your workflow and the difference compounds. To keep leveling up, the SQL intermediate course drills these patterns, and our common SQL mistakes guide shows what it looks like when they are ignored.