Data & Databases
SQL Best Practices: Writing Queries That Are Correct, Fast and Maintainable
Intermediate10 min readReviewed by Mariem Ouled on Updated
Most database performance problems are caused by how queries and schemas are written, not by the engine. This guide gives you the habits that keep SQL correct and fast on PostgreSQL and Oracle: fetching only what you need, writing predicates the optimizer can use, handling NULL honestly, choosing between EXISTS and IN, using bind parameters, paginating with keysets and batching writes.
What you will learn
- Write predicates that let the optimizer use an index instead of scanning the table
- Explain three-valued logic and avoid the classic NULL mistakes with NOT IN and comparisons
- Choose between JOIN, EXISTS and IN for the result you actually want
- Use bind parameters, keyset pagination and batched writes from application code
- Recognise the N+1 query pattern and replace it with a single set-based query
SQL is a declarative language: you describe the result you want and the database's optimizer decides how to produce it. That is liberating, and it is also why so much slow SQL looks perfectly reasonable. The optimizer can only choose a good plan if the query gives it the chance, the schema gives it the indexes, and the application does not undo everything by calling the query a thousand times in a loop. The practices below apply to any relational engine; examples use PostgreSQL 16 syntax, with Oracle differences noted where they matter.
Ask for exactly the rows and columns you need
The cheapest row is the one you never fetch. Two habits follow directly.
Name your columns. SELECT * transfers columns you will not use, defeats index-only scans (the index cannot satisfy a query that touches columns it does not contain), and couples your code to column order, which bites the day someone adds a column in the middle of the table.
Filter and aggregate in the database, not in the application. Pulling 200,000 rows into memory to sum a column in Java is slower, uses more memory, and holds a connection longer than SELECT SUM(amount) FROM payment WHERE .... The same logic applies to sorting and to counting: ORDER BY and COUNT(*) belong in SQL, where they can use indexes and parallel workers.
Object-relational mappers make it easy to get this wrong. With a lazy collection, iterating over 1,000 orders and touching order.getLines() on each issues 1,001 queries. This is the N+1 problem, and the fix is always the same: one query that joins or fetches the child rows in a set.
-- One round trip instead of 1,001
SELECT o.id, o.placed_at, l.sku, l.quantity, l.unit_price
FROM customer_order o
JOIN order_line l ON l.order_id = o.id
WHERE o.customer_id = $1
ORDER BY o.placed_at DESC, o.id, l.line_no;
In JPA that means a JOIN FETCH or an entity graph; in Spring Data it is @EntityGraph or a projection. The Spring Data JPA guide shows both.
Write predicates the optimizer can use
An index on email is useless to the query WHERE LOWER(email) = '[email protected]'. The function transforms the column, so the stored index values no longer match what the comparison needs. A predicate that can be answered by looking up an index is called sargable (from "search argument"). The rule is simple: keep the column bare on one side of the operator and put the computation on the other side.
| Not sargable | Sargable equivalent |
|---|---|
WHERE LOWER(email) = '[email protected]' | WHERE email = '[email protected]' with a case-insensitive collation or a citext column, or an expression index on LOWER(email) |
WHERE placed_at::date = '2026-09-01' | WHERE placed_at >= '2026-09-01' AND placed_at < '2026-09-02' |
WHERE amount * 1.2 > 100 | WHERE amount > 100 / 1.2 |
WHERE TO_CHAR(placed_at, 'YYYY') = '2026' | WHERE placed_at >= DATE '2026-01-01' AND placed_at < DATE '2027-01-01' |
WHERE description LIKE '%invoice%' | full-text search or a trigram index; a leading wildcard cannot use a B-tree |
Implicit type conversion is the sneaky version of the same problem. If customer_ref is a VARCHAR column and you write WHERE customer_ref = 12345, PostgreSQL raises an error, but Oracle silently converts every row's customer_ref with TO_NUMBER and scans the table. Bind parameters with the correct JDBC type, and compare dates with date literals rather than strings.
Range predicates deserve one more note: an index on (status, placed_at) can answer WHERE status = 'PAID' AND placed_at > :since efficiently, but an index on (placed_at, status) cannot use the equality on status after the range on placed_at. Column order in a composite index is a design decision; the index and execution plan guide covers how to choose it.
Treat NULL as what it is: unknown
SQL uses three-valued logic. Any comparison with NULL is neither true nor false but UNKNOWN, and WHERE only keeps rows whose condition is TRUE. Every one of these surprises comes from that rule:
WHERE discount <> 0drops rows wherediscount IS NULL. If you mean "has no discount or a zero one", sayWHERE discount IS NULL OR discount = 0, or define the columnNOT NULL DEFAULT 0so the question never arises.NULL = NULLisUNKNOWN, which is whyWHERE a.col = b.colnever joins two NULLs. PostgreSQL offersIS NOT DISTINCT FROMwhen you genuinely want NULL-equals-NULL semantics.x NOT IN (SELECT y FROM t)returns nothing at all if anyyisNULL. UseNOT EXISTS.COUNT(col)skips NULLs;COUNT(*)counts rows.AVG(col)averages only non-NULL values, which may or may not be what the report wants.- Oracle treats the empty string
''asNULLinVARCHAR2columns. PostgreSQL does not. Code ported between them must not rely on either behaviour.
COALESCE(col, fallback) is the standard, portable way to substitute a value; Oracle's NVL and DECODE and MySQL's IFNULL do the same job with worse portability. Prefer CASE expressions for anything more complex than a single fallback.
Joins, EXISTS and IN
Use explicit ANSI join syntax (JOIN ... ON) rather than listing tables in FROM and joining in WHERE. It separates join conditions from filters, makes outer joins unambiguous, and is what every modern optimizer and every code reviewer expects. Oracle's (+) outer-join notation still works but is legacy.
Pick the join type for the result you want, not the one you always type:
INNER JOINwhen a row without a match should disappear.LEFT JOINwhen the left row must survive without a match. A filter on the right table inWHERE(rather than inON) turns a left join back into an inner join, because unmatched rows haveNULLthere and fail the filter.EXISTSwhen you are asking a yes/no question about related rows and do not need their columns. It stops at the first match and never duplicates rows, which a join would do if the child table has several matches.IN (subquery)for small, NULL-free value sets. Optimizers rewrite mostINsubqueries into semi-joins anyway, so the performance difference withEXISTSis usually nil; the semantic difference aroundNULLis not.
-- Customers with at least one unpaid invoice: no duplicates, no NULL trap
SELECT c.id, c.name
FROM customer c
WHERE EXISTS (
SELECT 1
FROM invoice i
WHERE i.customer_id = c.id
AND i.status = 'UNPAID'
);
Adding DISTINCT to make duplicates go away is a smell: the duplicates mean the join fans out, and DISTINCT hides the cause behind an extra sort. Likewise UNION deduplicates and sorts; UNION ALL does not, and is what you want when the two sides cannot overlap.
Bind parameters, always
Concatenating user input into SQL is the cause of SQL injection, still one of the OWASP Top 10 injection risks decades after prepared statements made it unnecessary. Bind parameters (? in JDBC, $1 in PostgreSQL's protocol, :name in Oracle and in Spring's NamedParameterJdbcTemplate) send the statement and the values separately, so a value can never change the statement's structure.
There is a performance reason too. Oracle caches parsed statements in the shared pool keyed by their text; a thousand literal-laden variants of the same query cause a thousand hard parses and latch contention. PostgreSQL's server-side prepared statements and the JDBC driver's prepareThreshold give the same benefit for repeated queries.
// JDBC: the value is a parameter, never part of the SQL text
try (PreparedStatement ps = conn.prepareStatement(
"SELECT id, total FROM customer_order WHERE customer_id = ? AND placed_at >= ?")) {
ps.setLong(1, customerId);
ps.setObject(2, since); // java.time.LocalDate maps to DATE
try (ResultSet rs = ps.executeQuery()) { /* ... */ }
}
The one thing you cannot bind is an identifier: table and column names must come from a whitelist in your code, never from input.
Pagination and sorting
ORDER BY on a large result without an index that already provides the order forces a sort, possibly spilling to disk. Sort only when the consumer needs order, and when you paginate, make sure the order is total: ORDER BY placed_at alone is ambiguous for rows with equal timestamps and can show the same row on two pages. Add the primary key as a tiebreaker.
OFFSET pagination reads and discards every row before the page; page 1,000 costs a thousand times page 1. Keyset pagination remembers the last row seen and asks for rows after it:
-- First page
SELECT id, placed_at, total
FROM customer_order
WHERE customer_id = $1
ORDER BY placed_at DESC, id DESC
LIMIT 20;
-- Next page: pass the last row's (placed_at, id) back in
SELECT id, placed_at, total
FROM customer_order
WHERE customer_id = $1
AND (placed_at, id) < ($2, $3) -- row-value comparison
ORDER BY placed_at DESC, id DESC
LIMIT 20;
With an index on (customer_id, placed_at DESC, id DESC) each page is a short index range scan regardless of depth. Oracle uses FETCH FIRST 20 ROWS ONLY (12c and later) instead of LIMIT; the keyset idea is identical.
Transactions and batching from application code
Every statement runs in a transaction; the question is whether you control its boundaries. Auto-commit turns a loop of 10,000 inserts into 10,000 transactions, each waiting for a log flush. Wrap the batch in one transaction and use the driver's batch API so statements travel in bulk:
conn.setAutoCommit(false);
try (PreparedStatement ps = conn.prepareStatement(
"INSERT INTO event (id, occurred_at, payload) VALUES (?, ?, ?::jsonb)")) {
for (Event e : events) {
ps.setObject(1, e.id()); // UUID
ps.setObject(2, e.occurredAt()); // OffsetDateTime -> timestamptz
ps.setString(3, e.payloadJson());
ps.addBatch();
if (++n % 500 == 0) ps.executeBatch(); // keep memory bounded
}
ps.executeBatch();
conn.commit();
}
The PostgreSQL JDBC driver rewrites batched inserts into multi-row INSERT statements when reWriteBatchedInserts=true; Oracle's driver batches natively. Either way, keep transactions short: a transaction that stays open while the application talks to a remote API holds locks and, in PostgreSQL, blocks vacuum from cleaning up.
For updates that touch a large share of a table, one set-based UPDATE ... WHERE beats any loop, and for truly huge changes consider chunking by key range so each transaction stays small enough not to bloat the undo log or the WAL.
Schema decisions that shape every query
Some "query" problems are really model problems. Three rules avoid most of them:
- Declare foreign keys, and index the referencing column. Without the constraint, orphans creep in and the optimizer loses information it can use for join elimination. Without the index, every delete on the parent table scans the child table to check for references.
- Use the right type. Dates in
DATE/TIMESTAMPcolumns, not strings; money inNUMERIC, not floating point; identifiers with the same type on both sides of every join so no conversion is needed. If you generate identifiers in the application,UUIDcolumns store them in 16 bytes rather than 36 characters (a UUID generator is handy when seeding test data). - Plan for growth and purge. Tables that only ever grow need a retention policy from day one; deleting 100 million rows later is far harder than dropping a partition.
The normalization guide covers the design side in depth.
Common mistakes
- Function on the indexed column.
WHERE UPPER(name) = 'SMITH'scans. Store the data in the form you search it, or create an expression index onUPPER(name). - Filtering the outer side of a LEFT JOIN in WHERE.
LEFT JOIN address a ON ... WHERE a.country = 'FR'silently becomes an inner join. Move the condition into theONclause. NOT INwith a nullable subquery. Returns nothing. UseNOT EXISTS.- Fetching, then filtering in code.
findAll()followed by a stream filter moves the whole table across the network. Push the predicate into the query. ORDER BYwithout a tiebreaker in paginated queries. Rows repeat or vanish between pages. Add the primary key.- Hints as the first response to slowness. An Oracle
/*+ PARALLEL */or a forced index hides the bad predicate that caused the problem and stops working the day the data distribution changes. Fix the query, then measure with the plan. - Joining views on views. Generic views drag in columns and joins the query does not need, and the optimizer cannot always prune them. Query the base tables or write a purpose-built view.
Best practices
- Format SQL consistently and alias tables with meaningful short names (
customer_order o, nott1); a query is read far more often than it is written. - Prefer standard SQL (
COALESCE,CASE,FETCH FIRST, ANSI joins) over vendor functions unless the vendor feature buys real performance. - Look at the execution plan of every query that touches a large table before it ships, and again when the data grows tenfold.
- Keep transactions short and boundaries explicit; never hold one open across a user interaction or an external call.
- Let the database enforce integrity:
NOT NULL,CHECK,UNIQUEand foreign keys catch bugs that application validation misses under concurrency. - Store JSON in a
jsonbcolumn only for genuinely variable data; relational columns remain faster to index, constrain and query. When you do, validate documents with a JSON formatter before debugging a malformed insert.
Key takeaways
- Fetch only the columns and rows you need, and do filtering, sorting and aggregation in the database.
- A predicate is sargable when the indexed column stands alone on one side of the comparison; functions and implicit conversions on the column force a scan.
NULLis unknown: comparisons with it yieldUNKNOWN,NOT INwith a NULL subquery returns nothing, andCOUNT(col)skips it.- Use
EXISTSfor "is there any related row",JOINwhen you need the related columns, andUNION ALLunless you really need deduplication. - Bind every value; concatenation is both an injection risk and a parsing-cache killer.
- Paginate with keysets and a total order, not with deep
OFFSET. - Batch writes inside explicit, short transactions, and index every foreign key.
Sources and further reading
Frequently asked questions
- Is SELECT * really that bad?
- In an ad-hoc console it is fine. In application code it costs you in three ways: more bytes over the network and more memory in the driver, a query that silently breaks when a column is added or reordered (positional mapping, INSERT ... SELECT), and the loss of index-only scans, which need every referenced column to be in the index. Name the columns you use.
- Why does my query return no rows when I use NOT IN with a subquery?
- Because the subquery returns at least one NULL. In three-valued logic, x NOT IN (1, 2, NULL) evaluates to UNKNOWN for every x, and UNKNOWN never passes a WHERE clause. Use NOT EXISTS, which has no such trap, or add WHERE col IS NOT NULL to the subquery.
- Should I use OFFSET for pagination?
- OFFSET works for small, shallow pages but degrades linearly: page 500 of a 20-row list makes the database read and discard 10,000 rows. Keyset pagination (WHERE (created_at, id) < (:last_created_at, :last_id) ORDER BY created_at DESC, id DESC LIMIT 20) reads only the rows it returns and is stable when rows are inserted between requests.
- Are stored procedures a best practice or an anti-pattern?
- Neither by themselves. They are the right tool when a set-based operation must run close to the data (large batch updates, reporting aggregations) and the wrong tool when they hide business rules from the application's tests and version control. Keep them small, keep them in source control and treat them as an API of the database.