Data & Databases

SQL Best Practices: Writing Queries That Are Correct, Fast and Maintainable

Intermediate10 min readReviewed by 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 sargableSargable 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 > 100WHERE 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 <> 0 drops rows where discount IS NULL. If you mean "has no discount or a zero one", say WHERE discount IS NULL OR discount = 0, or define the column NOT NULL DEFAULT 0 so the question never arises.
  • NULL = NULL is UNKNOWN, which is why WHERE a.col = b.col never joins two NULLs. PostgreSQL offers IS NOT DISTINCT FROM when you genuinely want NULL-equals-NULL semantics.
  • x NOT IN (SELECT y FROM t) returns nothing at all if any y is NULL. Use NOT 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 '' as NULL in VARCHAR2 columns. 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 JOIN when a row without a match should disappear.
  • LEFT JOIN when the left row must survive without a match. A filter on the right table in WHERE (rather than in ON) turns a left join back into an inner join, because unmatched rows have NULL there and fail the filter.
  • EXISTS when 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 most IN subqueries into semi-joins anyway, so the performance difference with EXISTS is usually nil; the semantic difference around NULL is 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:

  1. 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.
  2. Use the right type. Dates in DATE/TIMESTAMP columns, not strings; money in NUMERIC, 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, UUID columns store them in 16 bytes rather than 36 characters (a UUID generator is handy when seeding test data).
  3. 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 on UPPER(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 the ON clause.
  • NOT IN with a nullable subquery. Returns nothing. Use NOT 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 BY without 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, not t1); 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, UNIQUE and foreign keys catch bugs that application validation misses under concurrency.
  • Store JSON in a jsonb column 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.
  • NULL is unknown: comparisons with it yield UNKNOWN, NOT IN with a NULL subquery returns nothing, and COUNT(col) skips it.
  • Use EXISTS for "is there any related row", JOIN when you need the related columns, and UNION ALL unless 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.