Queries written to sit close to the data: fast, direct, and legible enough that the next person to open them doesn't have to guess what they do.
BlackTor's work is covered by client NDAs, so the examples below don't describe a real client, project or dataset. To keep things concrete, they're all set at "Ride Me Cycles": a fictitious multi-branch bike retailer invented for this site. The techniques are real; Ride Me Cycles and everything about it are not.
Latest: live queries running straight from Excel into an Access billing database, reporting without leaving the spreadsheet.
Illustrative, per the note above: not real client work.
The problem: Ride Me Cycles' group P&L was rebuilt from six branch spreadsheets every week, and the numbers never quite matched head office's ledger.
The approach: Replaced the manual pull with a parameterised query straight against the sales database, so the P&L and the ledger read from the same source.
The result: One number, checked once, not reconciled six ways.
The problem: Finding one mechanic's full service history across two years of archived job tables meant opening each one in turn.
The approach: Built a UNION ALL view across the archive tables with an indexed date column, turning a half-hour search into a single query.
The result: Two years of history, one lookup.
The problem: A duplicate part-number problem was quietly inflating Ride Me Cycles' parts reordering, and nobody could say by how much.
The approach: Wrote a query that grouped parts by matching name and supplier fragments, surfacing every likely duplicate with the value at stake next to it.
The result: The scale of the problem, on one screen, before anyone reordered a part.
Every query below runs against the same four Ride Me Cycles tables: Branches(branch_id, branch_name, region), Staff(staff_id, branch_id, first_name, last_name), Products(product_id, product_name, category, list_price) and Sales(sale_id, sale_date, branch_id, staff_id, product_id, quantity, revenue, cost). Between them they cover the JOIN patterns that come up in almost every reporting query: turning foreign keys into readable names, finding rows with no match on the other side, joining before aggregating, ranking within a group, and comparing a table against itself.
Sales only stores branch_id, staff_id and product_id: correct for storage, useless on a printed report. Three INNER JOINs pull in the readable name that belongs to each id, and since every sale is expected to have a real branch, a real member of staff and a real product, INNER JOIN is the right choice here: a sale that somehow failed to match one of the three would be a data problem worth surfacing by disappearing from the report, not one worth hiding behind a LEFT JOIN.
An INNER JOIN from Staff to Sales would only ever return staff who HAVE sold something, since it drops any Staff row without a match. LEFT JOIN keeps every Staff row regardless, filling in NULLs where Sales has nothing to offer, so filtering afterwards for WHERE s.sale_id IS NULL isolates exactly the staff with no matching sale: an anti-join. The 30-day filter has to live in the ON clause here, not the WHERE clause: putting it in WHERE would silently discard the very NULL rows this query exists to find, turning the LEFT JOIN back into an INNER JOIN by accident. That distinction, and NULL's refusal to equal anything including itself, is the same one covered in the "NULL does not equal NULL" note opposite.
The join happens first, at row level, widening each sale with its branch name and product category; GROUP BY then collapses those widened rows down to one per branch/category pair, and the aggregate functions run over each group. NULLIF(SUM(s.revenue), 0) guards the margin calculation against a divide-by-zero on a branch/category combination with cost but no matching revenue, returning NULL for that row instead of raising an error.
RANK() OVER (PARTITION BY ...) runs after the join and the GROUP BY have produced one row per branch/product, numbering each product 1, 2, 3... within its own branch by revenue, without collapsing the branches into a single ranking. A window function's result can't be filtered directly in the same SELECT's WHERE clause, which is why the ranked query sits inside a subquery: the outer SELECT filters the already-ranked rows down to rank_in_branch = 1, one best-seller per branch.
Sales is joined to itself under two aliases, s1 and s2, to compare rows against other rows in the same table: here, two different staff members logging the same product, same branch, same day and same quantity, a pattern worth a human glance in case one entry is a duplicate till ring-up rather than two genuine sales. s1.staff_id < s2.staff_id does two jobs at once: it stops a row matching itself (staff_id always equals itself), and it stops the same pair coming back twice in mirrored order (once as A-then-B, once as B-then-A).
The sidebar carries the short version; this is the longer one, for whoever wants the detail behind it.
When a query is slow, the instinct is often to start rewriting the SQL: restructuring joins, adding hints, or breaking the statement into smaller pieces. In most cases this effort is misplaced. The far more common cause is that the database has no efficient way to find the rows it needs, because a column used in a WHERE or JOIN clause has no supporting index. Without one, the engine has to scan every row in the table to work out which ones qualify, and no amount of rewording the query text changes that underlying access path.
The fix, when this is the cause, is usually small: a single index on the right column, or a composite index covering the columns used together in a filter. The effect can be dramatic, turning a scan of the whole table into a direct seek to the handful of rows that matter, often taking a query from seconds to milliseconds without a single line of the query itself changing. It is worth checking this before anything else, because it is quick to test and, if it is the answer, it makes any planned rewrite unnecessary.
This is not a case for indexing every column just in case. Each index has to be maintained on every insert, update and delete, so adding them without reason slows down writes and adds storage overhead for no benefit. The point is to check first, with evidence such as a query plan, rather than assuming the index is missing or assuming the query text is at fault. Only once a missing index has been ruled out does it make sense to look at the structure of the query itself.
SELECT * is a natural habit when exploring a table for the first time, since it shows everything without having to know the column names in advance. Used in code that runs in production, though, it creates a dependency on the table's current shape that is easy to forget about. If a column is added, removed, renamed or reordered later, any code relying on SELECT * can silently start receiving different data than it expects, sometimes breaking a downstream process that was never touched, simply because the table it reads from changed.
Naming columns explicitly avoids this. The query only ever returns what it asks for, in a fixed order, regardless of what else happens to the table structure over time. It also means the database only has to read and transmit the columns actually needed, rather than every column in the row, which matters more than it might seem once a table includes large text fields, binary data, or simply many columns that a given report never uses. Pulling unnecessary data across the network adds latency for no benefit and puts unnecessary load on the server doing the reading.
The one place SELECT * remains reasonable is ad hoc, interactive exploration of a table whose shape is not yet known, where the query is thrown away afterwards. Anything that gets saved, scheduled, or embedded in application code should name its columns, precisely because that is the code most likely to still be running long after the table has changed.
Parameterised queries are usually introduced as a security measure, and that reason alone is sufficient: building SQL by concatenating strings together, including user input, opens the door to SQL injection, where a carefully crafted input value changes the meaning of the query itself rather than simply supplying a value. Parameters keep the query structure and the data strictly separate, so a value can never be interpreted as part of the SQL statement.
There is a second, less obvious benefit. Most databases cache the execution plan they work out for a query, keyed on the text of the query itself, so that a repeated query does not have to be replanned from scratch each time. A query built by concatenating literal values into the SQL text produces a slightly different string on every execution, so the cache treats each one as a new query, replans it every time, and fills the plan cache with near-duplicate entries that are rarely reused. A parameterised query keeps the SQL text identical across calls, so the same cached plan is reused for every execution, which is usually faster once a query runs more than a handful of times.
This is not entirely free of edge cases. A plan cached from the first execution of a parameterised query is chosen based on the values supplied at that time, and if later calls use very different values, for example a highly selective value followed by one matching most of the table, the cached plan can turn out to be a poor fit for the later call. This is worth knowing about, but it does not change the general rule: parameterise by default, and only investigate plan behaviour if a specific query shows signs of this kind of mismatch.
A tool such as EXPLAIN, or its equivalent that also runs the query and reports actual figures, shows what the database engine intends to do, or actually did, to satisfy a query: which indexes it used, if any; whether it scanned a whole table or sought directly to matching rows; and which join strategy it chose between the tables involved. Reading this before making any change turns a guess about why a query is slow into something closer to a diagnosis.
It is easy to assume a slow query needs restructuring, when the plan often points somewhere much simpler: a missing index, a join order the optimiser was forced into by a lack of statistics, or a mismatch between the number of rows the optimiser expected and the number it actually encountered. That last point is particularly useful. When the estimated row count in a plan differs wildly from the actual count, it usually means the table's statistics are out of date, and refreshing them can change the plan the optimiser chooses without any change to the query text at all.
Reading a plan well takes some familiarity with the terms a given database uses for its scan and join types, but the basic habit is what matters most: look at the plan before touching the query. It stops time being spent restructuring SQL that was never the actual bottleneck, and it also means that when a rewrite genuinely is needed, it is aimed at the specific operation the plan identifies as expensive, rather than a general guess at what might help.
SQL uses three valued logic rather than the simple true and false that most programming languages use: a condition can be true, false, or unknown, and any comparison involving NULL, including NULL compared to itself, evaluates to unknown rather than true. This is why a plain equals sign never matches a NULL value, no matter what it is compared against, and why IS NULL and IS NOT NULL exist as dedicated operators: they are the only constructs designed to test for the presence or absence of a value rather than compare values against each other.
The most damaging place this shows up is NOT IN. If the list of values NOT IN is checking against comes from a subquery, and that subquery returns even a single NULL among its results, the entire NOT IN condition stops matching any row at all, because the comparison against the NULL is unknown rather than false, and unknown propagates through the logic to make the whole condition unable to be certain of a match. This tends to surface as a report that quietly returns no rows, or far fewer than expected, with no error raised anywhere, which makes it a particularly easy bug to miss during testing on clean data and only discover once a real NULL turns up in production.
The safer alternative in that situation is NOT EXISTS, which does not carry the same NULL sensitivity, or explicitly filtering NULLs out of the list before using NOT IN. Either way, the underlying discipline is the same: treat NULL as the absence of a known value rather than as a value in its own right, and use the operators built for that purpose rather than an equals or not equals sign.
A correlated subquery is one that references a column from the outer query, which means it cannot be evaluated once and reused; conceptually, it has to run again for every row the outer query produces. Where the same logic can instead be expressed as a JOIN between the two tables, the database's optimiser has far more freedom in how it satisfies the request. It can choose a hash join, a merge join, or a nested loop, whichever suits the size and indexing of the tables involved, rather than being locked into row by row evaluation.
This difference tends to matter more as the outer result set grows. A correlated subquery that performs acceptably against a few hundred rows can become noticeably slower against tens of thousands, precisely because the per row cost of the subquery is being paid that many times over, whereas a JOIN's cost typically grows in a much more favourable way as the optimiser applies set based operations across the whole comparison at once.
This is not an argument against subqueries generally, and it does not apply as strongly to EXISTS, which the optimiser can often plan as an efficient semi-join rather than a genuinely repeated evaluation. The distinction worth keeping in mind is between logic that is naturally row by row, where a correlated subquery may be the clearest way to write it, and logic that is really a relationship between two sets of rows, where a JOIN is both easier for a reader to follow and more amenable to the optimiser doing its job well.
A transaction groups a set of statements so that they are treated as a single unit: either all of them take effect, or none of them do. This matters most when a single logical change touches more than one table, because without a transaction, each statement commits on its own as soon as it runs. If the first update succeeds and a second one then fails, for whatever reason, the database is left in a state that reflects only part of the intended change, which is often worse than if nothing had happened at all, since nothing in the data indicates that it is incomplete.
Wrapping the statements in a transaction and only committing once every statement in the group has succeeded means a failure partway through can simply be rolled back, restoring the data to exactly the state it was in before any of the statements ran. This is the atomicity that transactions are built to guarantee, and it removes an entire category of data integrity problem that is otherwise very difficult to detect after the fact, since a half applied change looks like valid data rather than an obvious error.
The main practical caution is to keep transactions as short as reasonably possible. A transaction typically holds locks on the rows or tables it touches for its entire duration, so a transaction left open longer than necessary, for example while waiting on something outside the database, can block other work and cause contention. The goal is not to wrap everything in one large transaction regardless of scope, but to make sure that any change which genuinely needs to succeed or fail as a whole is protected as one.
Once a moderately complex query, involving several joins, filters and calculated columns, is being used in more than one report, keeping it as a view rather than copying the same SQL into every place that needs it has a clear practical benefit: there is only one definition to get right, and only one place to fix it when a requirement changes. Without a view, the same logic tends to drift slightly between copies over time, as one report gets updated and another does not, until two reports that are meant to answer the same question quietly stop agreeing with each other.
A view is, in most databases, simply a stored query definition rather than a stored copy of data; querying it runs the underlying SQL each time, so it carries no particular performance cost or benefit of its own beyond what the same query would cost if run directly. That is worth knowing, because it means a view does not solve a performance problem by existing, only a maintenance one. Where a genuinely materialised, precomputed result is needed, that is a different and more deliberate decision, typically involving a materialised view or a summary table that is refreshed on a schedule.
The one thing worth watching for is layering views on top of other views many levels deep. Each layer can make the SQL more pleasant to read at that level, but the optimiser still has to work through the whole expanded query underneath, and a deeply nested stack of views can end up harder to reason about, and sometimes harder to optimise, than the equivalent logic written out directly. Used for genuinely shared, reused logic, though, a view is usually the simplest way to keep that logic consistent.
UNION and UNION ALL both combine the results of two or more queries into a single result set, but UNION does additional work that UNION ALL does not: it removes duplicate rows from the combined result, which in practice means the database has to sort or hash the entire combined set to identify which rows match, before it can return anything. On a small result this cost is negligible, but on a large one it adds a genuinely expensive step to a query that, in many cases, did not need it in the first place.
The deduplication is only necessary if duplicate rows can actually occur across the queries being combined, and in a large proportion of real cases they cannot. Combining a table of current records with an archive table of historical ones, for example, where a primary key never appears in both at once, cannot produce a genuine duplicate row across the two result sets, so UNION's extra work buys nothing. UNION ALL returns exactly the same rows, without the sort or hash step, and is correspondingly faster.
The rule of thumb is therefore to reach for UNION ALL by default, and only use UNION where duplicates are genuinely possible and genuinely need to be removed for the result to be correct, or occasionally where a small amount of harmless duplication would still be misleading to a reader even if it does not affect a downstream calculation. Checking which situation applies before choosing between them is a small piece of analysis that is easy to skip, but it is the difference between a query that scales comfortably and one that quietly gets slower as the underlying tables grow.
Indexes and query plans are built around the assumption that a table has some particular size and distribution of data, and both change as a table grows. A table that has been accumulating rows for years without ever having old data archived or partitioned out eventually reaches a size where even a well indexed, perfectly ordinary query starts to feel slow, simply because there is more of everything to work through: deeper indexes to traverse, more pages to read from disk, and more contention with other queries running against the same large table at the same time.
This tends to creep up gradually rather than announce itself. A query that has run acceptably for years can slow down by a small, easy to dismiss amount every few months as the table grows, until enough time has passed that it has become noticeably, sometimes suddenly, unacceptable, at which point the underlying cause is often mistaken for something else entirely, such as a recent code change, when the real cause is simply the accumulated size of the table.
Reviewing what data genuinely still needs to sit in the live, actively queried table, and moving anything older into an archive table, a separate database, or a partition that can be excluded from routine queries, keeps this from happening unnoticed. Table partitioning by a natural boundary such as date is a particularly effective version of this where it is supported, since it lets old partitions be archived, compressed, or dropped without touching the current data at all, and lets the query engine skip entire partitions it can tell in advance are irrelevant to a given query. Doing this on a regular schedule, rather than only once performance has already become a visible problem, is what keeps table growth from becoming a recurring source of unexplained slowdown.