Most SQL tutorials treat subqueries (a query nested inside another query) like a topic you learn once and never come back to. In real work, they are the second tool you reach for, after a JOIN, when the data shape does not match the question you are asking. This guide is a working walkthrough of when a subquery is the cleanest option, when it is the wrong choice, and the three positions (SELECT, FROM, WHERE) where they show up most often.
The framing here is built around one idea: a subquery is just a query that produces a value, a row, or a table, and the position in the outer query determines what it produces. Once you see the position, the rest of the syntax is small. The source material for this rewrite is a freeCodeCamp walkthrough by Abdullah Rufai, with the examples rewritten for clarity and the structure reorganized so the order matches how the syntax actually builds up.
What a subquery actually is
A subquery is a SELECT statement written inside parentheses and used as input to another SELECT, INSERT, UPDATE, or DELETE statement. The outer query is usually called the main query or the outer query. The inner one is the subquery or the inner query. The parentheses are not decorative: they tell the parser “evaluate this part first, then use its result in the place where it sits.”
The simplest version of this is a filter. Suppose you have a student table with a location column and a registration table with student_id. You want every registration row whose student lives in Lagos. Without a subquery, you would JOIN the two tables. With one, you write:
SELECT *
FROM registration
WHERE student_id IN (
SELECT id
FROM student
WHERE location = 'Lagos'
);
The subquery returns a list of student IDs. The main query then asks the database to keep every registration row whose student_id is in that list. The execution order is subquery first, then main query.
That order matters because of a common follow-up question: why not just look up the Lagos IDs by hand and paste them into the WHERE clause? You could, the day you write the query. The problem is that a hardcoded list does not update when new Lagos students are added. The subquery re-runs every time the query executes, so the result always reflects the current state of the student table.
Three positions, three roles
A subquery can sit in three places inside a main query, and the position is what determines what it produces. The same nested SELECT behaves completely differently depending on where it lives.
A subquery in the SELECT list is a derived column (a column that is calculated inside the query rather than stored in the database). It runs once per row of the outer query’s result, or it can be written to run once total if it has no outer reference. The classic use case is adding a total that has to appear on every row, like a percentage-of-total calculation:
SELECT
course_name,
COUNT(reg_id) AS regs,
(SELECT COUNT(reg_id) FROM registration) AS total
FROM course AS l
LEFT JOIN registration AS r
ON l.id = r.course_id
GROUP BY course_name;
The subquery (SELECT COUNT(reg_id) FROM registration) does not depend on the outer row, so the database computes it once and pastes the same value into every row of the result. That makes it easy to divide per-row counts by a shared denominator without a second pass through the data.
A subquery in the FROM clause is a derived table (a temporary table built by the query and thrown away when the query ends). It must be wrapped in parentheses and given an alias, the same way any other table in a FROM clause needs a name. The alias is what the outer query uses to reference the result. The classic use case is reshaping a table that does not have the column you wish it had:
SELECT region, COUNT(id) AS students
FROM (
SELECT
*,
CASE
WHEN location IN ('Abeokuta','Ibadan','Mokola','Lagos')
THEN 'West'
WHEN location IN ('Anambra','Owerri','Enugu','Port Harcourt')
THEN 'East'
ELSE 'North'
END AS region
FROM student
) AS data_prep
GROUP BY region;
The inner query adds a region column on the fly, the outer query groups by it, and the result is the number of students in each region. The data_prep alias is required: most databases refuse to reference an unnamed derived table in the outer query.
A subquery in the WHERE clause is a filter. It is the version most people learn first, because it is the version that most often replaces a hardcoded list. The operators you use with it split into two camps: logical operators like IN, ANY, and ALL for multi-row results, and comparison operators like =, >, and < for single-value results.
When the subquery depends on the outer row
The examples so far have been non-correlated subqueries: the inner query runs on its own, produces a result, and the outer query uses that result. The other kind is the correlated subquery, where the inner query references a column from the outer query and therefore has to be re-evaluated for every row the outer query produces.
A typical example is “every student who scored higher than the average score in their own course”:
SELECT s.*
FROM student AS s
JOIN score AS sc ON sc.student_id = s.id
WHERE sc.score > (
SELECT AVG(sc2.score)
FROM score AS sc2
WHERE sc2.course_id = sc.course_id
);
The inner SELECT AVG(sc2.score) references sc.course_id from the outer query. That means the database cannot compute the subquery once and reuse the result; it has to compute a separate average for every distinct course the outer query encounters. On a small table this is invisible. On a large table, correlated subqueries are the most common cause of “why is this query so slow” moments, and the fix is usually to rewrite the same logic as a JOIN against a GROUP BY in a derived table.
The rule of thumb worth keeping: if the inner query can be written without any column from the outer query, it is non-correlated and the database evaluates it once. If it cannot, it is correlated and the database evaluates it once per outer row.
Trade-offs
Subqueries are not free. The cleanest version of the trade is in three parts.
Performance is the obvious one. A correlated subquery that runs once per outer row is, in execution terms, a loop inside a loop. For small tables that loop is invisible. For large tables it is the difference between a query that returns in 200 milliseconds and a query that times out the dashboard. Most correlated subqueries can be rewritten as a JOIN with a GROUP BY, and the JOIN version usually wins. The non-correlated versions are usually fine.
Readability is the second. A subquery in the SELECT list, written inline, is a clean way to add a constant total. A correlated subquery three levels deep, in a WHERE clause, is hard to read and harder to debug. When the same logic can be expressed with a CTE (a Common Table Expression, a named subquery declared with WITH that the outer query can reference by name) or a JOIN, prefer that. CTEs were added to SQL for exactly this reason.
Portability across database engines is the third. Every modern database supports the three positions above, but the optimizer (the database component that decides how to actually execute the query, including the order of joins and the use of indexes) is different in PostgreSQL, MySQL, SQLite, and SQL Server. A subquery that runs in 50ms in one engine can take 5 seconds in another because of how the optimizer chooses to flatten it. If you are writing SQL that has to run on more than one engine, test the slow-looking ones on real data before shipping the query into a hot path.
The one thing subqueries are uniquely good at is producing a single value that has to appear on every row of the outer query, like the percentage-of-total example above. That shape is awkward to express with a JOIN, and the subquery version reads the way the calculation actually works.
Bottom line
A subquery is a SELECT used as input to another statement. Its position in the outer query is what determines its role: a derived column in SELECT, a derived table in FROM, or a filter in WHERE. If it does not depend on the outer row it is non-correlated and runs once. If it does, it is correlated and runs per row.
A few practical pointers for the next query you write:
- Start with a JOIN. Most queries that look like a subquery filter are easier to read and faster to execute as a JOIN. Reach for the subquery only when the JOIN would be longer or less clear.
- Use a derived table when the input needs a column that does not exist yet. The CASE-built
regioncolumn above is the canonical pattern: build the missing column in a subquery, alias the result, group on it in the outer query. - Rewrite correlated subqueries as JOIN + GROUP BY once they go past a few thousand rows. The loop-in-a-loop cost is real and easy to forget.
- Test on the actual database you ship to. Optimizers differ across engines, and a subquery that is fast in PostgreSQL can be slow in MySQL for reasons that have nothing to do with your SQL.
Subqueries are not exotic. They are one of two ways to combine data from multiple tables (JOIN being the other), and the choice between them is usually a readability call, not a performance call. Pick the version the next person to read the query will understand in five seconds.