Correct SQL and good SQL are not the same thing. A query can return the right rows and still be slow, unreadable, and a trap for the next person who edits it. This guide collects the practical techniques that separate the two, each with a before and after you can copy straight into your own work.
Every technique here follows one idea: say clearly what you mean, give the engine predicates it can use, and never make it do work you can avoid. These habits pair naturally with our broader SQL best practices and the deeper SQL performance tuning guide. Read this to sharpen the queries you write every day.
What this guide covers
- Name your columns, skip SELECT *
- Write sargable predicates
- Filter early, aggregate late
- Joins versus subqueries
- Use CTEs for readability
- Format and alias consistently
- Handle NULLs deliberately
- Prefer EXISTS over IN for big sets
- Paginate with keyset, not OFFSET
- Always read the plan
- The checklist and biggest wins
Name your columns, skip SELECT *
SELECT * feels convenient and costs you three ways. It drags every column across the network even when you use two of them, it defeats covering indexes because the index no longer contains everything the query needs, and it makes the query fragile: add a column to the table and result shapes shift underneath your application. Naming columns documents intent and lets the optimizer answer more queries from the index alone.
-- pulls every column, breaks index-only scans
SELECT * FROM customers WHERE country = 'IN';-- name exactly what you consume
SELECT id, email, created_at
FROM customers
WHERE country = 'IN';The one fair use of SELECT * is quick interactive exploration at the console. In views, application code, and anything that ships, list the columns. The DQL command reference walks through the full SELECT clause order if you want the mechanics.
Write sargable predicates
A predicate is sargable (Search ARGument able) when the engine can use an index to satisfy it. The most common self inflicted slowdown is wrapping the indexed column in a function or a cast, which forces the engine to compute the expression for every row and scan the whole table. The fix is to move the work off the column and onto the constant.
-- a function on the column kills the index
SELECT id FROM orders
WHERE YEAR(created_at) = 2026;-- an open ended range the B-tree can seek
SELECT id FROM orders
WHERE created_at >= '2026-01-01'
AND created_at < '2027-01-01';The same rule catches implicit type conversion. Comparing an indexed VARCHAR column to a numeric literal makes the engine cast the column, not the value, so the index goes unused. Match the literal to the column type instead. Leading wildcards are the other classic trap: LIKE 'abc%' can seek a prefix, but LIKE '%abc' cannot and scans the table. For a full treatment of index friendly predicates, Markus Winand's Use The Index, Luke is the definitive free reference.
Key idea: if you find yourself calling a function on the column side of a comparison, stop and ask whether the same test can be written as a plain range or equality on the raw column. It almost always can.
Filter early, aggregate late
The cheapest row to process is the one you discard first. Push every predicate as close to the scan as you can, so joins, sorts, and aggregations run over the smallest set possible. A filter in WHERE runs before grouping; a filter in HAVING runs after, over the aggregated result. Use HAVING only for conditions on the aggregate itself, and put everything else in WHERE.
-- groups every row, then throws most away
SELECT customer_id, SUM(total) AS spend
FROM orders
GROUP BY customer_id
HAVING customer_id IN (101, 102, 103);-- cut rows first, then aggregate the survivors
SELECT customer_id, SUM(total) AS spend
FROM orders
WHERE customer_id IN (101, 102, 103)
GROUP BY customer_id;Modern optimizers push predicates down automatically when it is provably safe, but a subquery boundary or a non sargable expression can block them. Writing the filter where it logically belongs removes any doubt and keeps the intent obvious to the next reader.
Joins versus subqueries
A great deal of tangled SQL is a correlated subquery that wanted to be a join. When you are combining columns from two tables, a JOIN states the relationship once and lets the optimizer choose the algorithm. A scalar subquery that runs per row can force a repeated lookup and hides the relationship inside the SELECT list.
-- a scalar subquery evaluated for every order row
SELECT o.id,
(SELECT c.name FROM customers c
WHERE c.id = o.customer_id) AS customer
FROM orders o;-- one join, the optimizer picks the strategy
SELECT o.id, c.name AS customer
FROM orders o
JOIN customers c ON c.id = o.customer_id;Subqueries still earn their place. A derived table that pre aggregates a one to many relationship before joining it avoids fan out, and a semi join test with EXISTS (covered in section 8) is exactly the right tool for existence checks. The rule of thumb: use a join to bring back columns, and a subquery to express a set membership or a pre aggregation. Our performance tuning guide digs into the join algorithms behind these choices.
Use CTEs for readability
A common table expression, written with WITH, names a step so a long query reads as a sequence of clear stages instead of nested parentheses. It is the difference between a query you can review in one pass and one you have to reverse engineer. Each CTE gets a name, so the logic reads top to bottom.
-- each stage is named and readable
WITH recent_orders AS (
SELECT customer_id, total
FROM orders
WHERE created_at >= '2026-01-01'
),
per_customer AS (
SELECT customer_id, SUM(total) AS spend
FROM recent_orders
GROUP BY customer_id
)
SELECT c.name, p.spend
FROM per_customer p
JOIN customers c ON c.id = p.customer_id
WHERE p.spend > 1000
ORDER BY p.spend DESC;Watch out: CTEs are for clarity, not a free performance win. Older PostgreSQL versions materialized every CTE as an optimization fence; today it can inline them, but a CTE referenced many times may still be computed more than once. If a stage is reused heavily and the plan shows repeated work, test it against a plain subquery or a temporary table.
Format and alias consistently
Readable SQL is faster to review, faster to debug, and less likely to hide a bug. Pick a house style and hold to it: keywords in one case, one clause per line, joins with explicit ON conditions, and short but meaningful table aliases. Above all, qualify every column with its table alias in any multi table query, so no reader has to guess where a column comes from.
-- unqualified columns, cramped, ambiguous
select name,total,city from orders o,customers c
where o.customer_id=c.id and total>100;-- explicit join, qualified columns, one clause per line
SELECT c.name, o.total, c.city
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.total > 100;The good version uses an explicit JOIN instead of a comma join in the FROM clause, which is easier to read and much harder to turn accidentally into a cross join. Aliases like o and c are fine when short; for wider queries prefer names that hint at the table. Keep a copy of our SQL cheat sheet nearby while a style settles into muscle memory.
Handle NULLs deliberately
NULL means unknown, and it does not behave like a value. Any comparison with = or <> against NULL yields unknown, not true, so those rows silently vanish from your result. You must test for it with IS NULL and IS NOT NULL, and remember that aggregates such as SUM and AVG skip nulls while COUNT(*) counts them.
-- rows where status IS NULL are silently dropped
SELECT id FROM tickets
WHERE status <> 'closed';-- decide explicitly what unknown status means
SELECT id FROM tickets
WHERE status <> 'closed'
OR status IS NULL;The table below shows how three way logic plays out so the surprises stop being surprises.
| Expression | Result | What to use instead |
|---|---|---|
x = NULL | unknown (never true) | x IS NULL |
x <> NULL | unknown (never true) | x IS NOT NULL |
NULL AND false | false | fine as written |
NULL OR true | true | fine as written |
SUM(col) with nulls | nulls ignored | COALESCE(col, 0) to treat as zero |
Use COALESCE to supply a default and keep it on the value side so you do not break an index. For a fuller walkthrough of three valued logic, see our dedicated explainer on common SQL mistakes, where mishandled nulls are a repeat offender.
Prefer EXISTS over IN for big sets
When you test whether a row has a match in another table, EXISTS and IN often look interchangeable, but they differ in two ways that matter at scale. EXISTS is a semi join: it stops at the first match and never builds the full list. And IN against a subquery that can return NULL has a nasty edge case, because NOT IN with a null in the list returns no rows at all.
-- NOT IN breaks if the subquery yields any NULL
SELECT c.id FROM customers c
WHERE c.id NOT IN (
SELECT o.customer_id FROM orders o
);-- NOT EXISTS is null safe and stops at first match
SELECT c.id FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.id
);On modern optimizers the positive forms, IN and EXISTS, frequently compile to the same plan, so readability can decide. The strong recommendation is for the negative case: reach for NOT EXISTS over NOT IN every time a subquery is involved, because it is correct in the presence of nulls and usually the safer choice for large inner sets.
Paginate with keyset, not OFFSET
LIMIT ... OFFSET n is a trap as pages get deep. To return page five thousand the engine must generate and discard every preceding row, so the further you page the slower it runs. Keyset or seek pagination remembers the last row shown and continues past it with a sargable WHERE, so every page costs the same.
-- reads and discards 100,000 rows to show 20
SELECT id, title FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;-- carry the last key, seek straight to the next page
SELECT id, title FROM posts
WHERE created_at < '2026-06-01 09:14:00'
ORDER BY created_at DESC
LIMIT 20;Keyset needs a stable, unique sort key. When the sort column can tie, append a tiebreaker such as the primary key so the seek is deterministic. The trade off is that keyset supports next and previous rather than jumping to an arbitrary page number, which is exactly what infinite scroll and feed style interfaces want anyway.
Always read the plan
Every technique so far is a hypothesis until the execution plan confirms it. EXPLAIN shows the plan the optimizer intends to run with estimates only, while EXPLAIN ANALYZE actually runs the query and reports real timings and row counts beside the estimates. Get the plan, find the fattest operator, fix that one thing, then measure again.
-- PostgreSQL: plan plus real timings and I/O
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total FROM orders
WHERE customer_id = 4217;
-- MySQL 8+: readable tree, then real execution
EXPLAIN FORMAT=TREE
SELECT id, total FROM orders WHERE customer_id = 4217;Two signals matter most. A large gap between estimated and actual rows means the optimizer is planning for the wrong data shape, usually stale statistics or a non sargable predicate. And a sequential scan that removes almost every row it reads is a missing index shouting at you. The official PostgreSQL EXPLAIN documentation explains how to read each node, and the MySQL reference manual covers the equivalent output for InnoDB. Build the habit of checking the plan before and after any change, and reserve deeper study for our performance tuning walkthrough.
The checklist and biggest wins
The techniques stack, but they do not pay equally. The chart below ranks how often each one, applied to typical everyday queries, produces a clear readability or performance improvement. Naming columns and writing sargable predicates are the reliable staples; the rest matter most in the situations they were built for.
These are rules of thumb, not laws; your schema and workload decide the real numbers. Keep the checklist below within reach and run through it whenever a query feels slow or hard to read.
| Technique | Why it helps | Impact |
|---|---|---|
Name columns, skip SELECT * | Less data moved, keeps covering indexes usable, stable result shape | High |
| Sargable predicates | Lets the engine use indexes instead of scanning | High |
| Filter early, aggregate late | Cuts rows before joins and grouping run | High |
| Joins over scalar subqueries | States the relationship once, optimizer picks the strategy | Medium |
| CTEs for readability | Names each stage so the query reads top to bottom | Medium |
| Consistent format and aliasing | Faster review, fewer ambiguous columns and stray cross joins | Medium |
Handle NULLs with IS NULL | Stops rows silently disappearing from results | Medium |
NOT EXISTS over NOT IN | Null safe and stops at the first match | Medium |
| Keyset pagination | Every page costs the same, no discarded rows | High on deep pages |
Read the plan with EXPLAIN | Turns guessing into measuring | High |
Better SQL is a set of habits, not a rewrite. Adopt these one at a time, confirm each with the plan, and your queries get faster and clearer at the same time. When you are ready to practice them under guidance, the SQL intermediate course works through joins, subqueries, and tuning hands on, and the SQL best practices guide places these techniques in the wider picture of writing SQL that lasts.