A query that returns the right answer is only half done. The other half is returning it without wasting work. Anti-patterns are habits that look harmless on small tables but collapse under real load: fetching columns you do not need, running a subquery once per row, or wrapping an indexed column in a function that hides it from the optimizer.
This lesson teaches you to spot the four most common culprits and rewrite them. The rewrites are usually shorter, always clearer, and often orders of magnitude faster. The goal is not memorisation — it is developing an instinct that looks at a query and asks, 'Is there a cheaper way to say the same thing?'
flowchart LR A["SELECT * FROM big_table"] -->|"rewrites to"| B["SELECT only needed columns"] C["correlated subquery"] -->|"rewrites to"| D["JOIN or GROUP BY"] E["function on indexed column"] -->|"rewrites to"| F["range or direct comparison"] style B fill:#0e7490,color:#fff style D fill:#0e7490,color:#fff style F fill:#0e7490,color:#fff
The cost of SELECT *
Fetching every column with SELECT * is convenient during exploration and expensive in production. The database must read wider rows, move more bytes across the network, and store larger result sets in memory. Worse, SELECT * prevents a covering index from answering the query, because the index almost certainly does not contain every column. The engine is forced back to the table even when a leaner query could have been satisfied from the index alone.
The fix is explicit: name the columns you need. It documents intent for the next reader, protects you from schema changes that add new columns, and lets the planner use the smallest possible access path. If you only need the name and salary, say so.
SELECT * FROM employees WHERE department_id = 1; SELECT name, salary FROM employees WHERE department_id = 1;
Measuring the waste
The penalty of SELECT * is invisible on our small seed table, but on a table with a hundred columns and millions of rows the difference is stark. Each extra column adds width to every row the database must read, sort, and transmit. A query that only needs two columns but asks for twenty can read ten times as much data as necessary.
The habit to build is to name columns explicitly from the first draft. It costs a few extra keystrokes and saves you from discovering later that a report is slow because it is dragging a dozen large text columns across the network that no one is looking at.
Correlated subqueries: once per row
A correlated subquery references the outer query and re-runs for every row. That expressiveness is powerful, but when the same result can be computed once and shared, the correlation becomes pure overhead. The classic example is fetching a related value, such as a department name for each project, by running a scalar subquery in the SELECT list.
A JOIN computes every department name once and matches it to the relevant projects. On large tables that set-based approach wins dramatically because the planner can use indexes on both tables and avoid re-executing the inner query. Any time a correlated subquery returns data rather than testing existence, ask whether a JOIN says the same thing more cheaply.
flowchart TD
subgraph COR["Correlated subquery"]
R1["row 1"] --> S1["lookup dept name"]
R2["row 2"] --> S2["lookup dept name"]
R3["row 3"] --> S3["lookup dept name"]
end
subgraph JOI["JOIN"]
L["lookup all dept names once"] --> M["match to rows"]
end
style COR fill:#b45309,color:#fff
style JOI fill:#0e7490,color:#fff
-- correlated: one subquery per project SELECT p.name, (SELECT d.name FROM departments d WHERE d.id = p.department_id) AS dept_name FROM projects p; -- JOIN: one pass through each table SELECT p.name, d.name AS dept_name FROM projects p JOIN departments d ON p.department_id = d.id;
When correlation is still the right choice
Correlation is not always bad. A correlated EXISTS subquery that tests for existence — WHERE EXISTS (SELECT 1 FROM ...) — is often rewritten by the planner into an efficient semi-join. The anti-pattern is specifically using a correlated scalar subquery to fetch data, because that pattern cannot be streamed and must be evaluated row by row. When you need a value from another table, reach for a JOIN. When you only need a yes-or-no test, EXISTS remains a clean and fast choice.
Functions on indexed columns
When you wrap a filtered column in a function, such as SUBSTR(hired_on, 1, 4) = '2020', the engine cannot use an index on hired_on. The index stores raw values, not the output of functions, so the optimizer discards it and scans the table. This is one of the most common ways to accidentally disable an index.
The rewrite is usually a range. WHERE hired_on BETWEEN '2020-01-01' AND '2020-12-31' expresses the same idea without a function, and an index on hired_on can serve it directly. The same rule applies to UPPER(name), arithmetic on prices, and any other transformation. Keep the column bare on one side of the comparison and let the index do its job.
flowchart LR Q["WHERE SUBSTR(hired_on,1,4) = '2020'"] --> S["SCAN TABLE<br/>index ignored"] Q2["WHERE hired_on BETWEEN<br/>'2020-01-01' AND '2020-12-31'"] --> I["SEARCH TABLE<br/>USING INDEX"] style S fill:#b45309,color:#fff style I fill:#0e7490,color:#fff
The habit of bare columns
The rule is simple: keep the column bare on at least one side of the comparison. WHERE column = value allows an index. WHERE function(column) = value does not. If you need case-insensitive matching, consider storing the data in the case you query it, or using a collation that handles comparison without a function wrapper.
The same principle applies to date truncation, arithmetic, and string parsing. Any time you find yourself wrapping a column in a function to make the filter work, pause and ask whether the data can be stored or indexed in a form that lets the comparison stay direct.
-- function disables any index on hired_on SELECT name, hired_on FROM employees WHERE SUBSTR(hired_on, 1, 4) = '2020'; -- range lets an index on hired_on do the work SELECT name, hired_on FROM employees WHERE hired_on BETWEEN '2020-01-01' AND '2020-12-31';
The query below fetches every column even though the report only needs names and roles. Rewrite it to select only name and role for employees in department 1.
SELECT * FROM employees WHERE department_id = 1;
Replace the asterisk with the two column names you need.
Keep the WHERE clause unchanged.
SELECT name, role FROM employees WHERE department_id = 1;
Rewrite this query that uses a correlated subquery in the SELECT list. Use a JOIN to fetch each project's name and its department name more efficiently.
SELECT p.name, (SELECT d.name FROM departments d WHERE d.id = p.department_id) AS dept_name FROM projects p;
Join projects to departments on department_id.
Select p.name and d.name.
SELECT p.name, d.name AS dept_name FROM projects p JOIN departments d ON p.department_id = d.id;
This query finds employees hired in 2020 by applying SUBSTR to the hired_on column. That prevents any index on hired_on from helping. Rewrite it using a date range instead.
SELECT name, hired_on FROM employees WHERE SUBSTR(hired_on, 1, 4) = '2020';
Use BETWEEN with the first and last day of 2020.
Keep the column bare on the left side.
SELECT name, hired_on FROM employees WHERE hired_on BETWEEN '2020-01-01' AND '2020-12-31';
This query finds employees who earn more than their department's average using a correlated subquery. Rewrite it as a JOIN against a derived table that computes each department's average once.
SELECT e.name, e.salary FROM employees e WHERE e.salary > ( SELECT AVG(salary) FROM employees WHERE department_id = e.department_id );
Use a subquery in the FROM clause to compute AVG(salary) per department_id.
Join that result to employees on department_id.
Filter with e.salary > dept_avg.avg_sal.
SELECT e.name, e.salary FROM employees e JOIN (SELECT department_id, AVG(salary) AS avg_sal FROM employees GROUP BY department_id) dept_avg ON e.department_id = dept_avg.department_id WHERE e.salary > dept_avg.avg_sal;
Two queries return the same results. Which is likely faster on a large table and why?
Query A: SELECT * FROM employees e WHERE EXISTS (SELECT 1 FROM departments d WHERE d.id = e.department_id AND d.city = 'Oslo')
Query B: SELECT e.* FROM employees e JOIN departments d ON e.department_id = d.id WHERE d.city = 'Oslo'
While modern optimisers may rewrite EXISTS internally, the JOIN is the idiomatic pattern for fetching data from two tables. It expresses the relationship explicitly and lets the planner choose the best index-driven path for both tables without the overhead of a correlated execution model.
Recap
SELECT *fetches unnecessary data and prevents covering indexes; name the columns you need.- Correlated subqueries in the SELECT list re-run for every row; a JOIN computes the lookup once.
- Functions on filtered columns, like
SUBSTR(hired_on, ...), disable indexes; rewrite as a range comparison. - Rewrites are not just about speed — they make intent clearer and queries easier to maintain.
Next you will weigh the trade-off between normalised design and denormalised speed, and learn when to break the rules on purpose.