Inside you there are two wolves, one wants to use a subquery and the other prefers a CTE. Both are SQL developers, and neither will stop arguing about which one is better. Today we will discuss which wolf to feed (depending on the situation).
What Are Subqueries?
A subquery is a query nested inside another query. It usually runs first and its result gets used by the outer query either as a value to compare against, a list to check against or a table to select from.
Subqueries can show up in a few places: the WHERE clause, the SELECT list, the FROM clause, or even inside another subquery.
The 'select avg(score)' in the WHERE clause is the subquery in this case and runs first.
What Are CTEs?
A Common Table Expression (WITH ... AS (...)) sets up a temporary, named result at the top of your query, which you can then use like a table further down. It's basically a subquery you pull out and give a name to, which makes longer queries much easier to read.
Differences between Subqueries and CTEs
Subquery
- Named? - No, it's just embedded inline.
- Reuse - Has to be rewritten if you need it more than once.
- Readability - Gets messy once you nest subqueries inside subqueries.
- Where it can go - WHERE, SELECT, FROM, or nested in another subquery.
Common Table Expressions
- Named? - Yes, you give it a name with WITH.
- Reuse - Defined once, can be referenced multiple times in the same query.
- Readability - Reads top-to-bottom as separate, labeled steps.
- Where it can go - Defined once at the top, used anywhere in the main query below it.
In short, subqueries are fine for a quick, one-off check. CTEs are worth it once a query has more than one logical step and you want to name and follow each one.
Practical Examples: Why One Over the Other
Each example below is a case where the subquery and CTE aren't just two ways of writing the same thing , one is actually a better fit, whether that's for speed or for readability.
1. Comparing every row against one single value
Sometimes you need to compare every row against one specific value pulled from the same table. In our case we want to find everyone that beat Felix's score in the first test.
The plain subquery is shorter and works fine if you're only using the value once. The CTE is preferable if you need felix_score more than once in the same query (if you also want a score - felix_score as different column) naming it once avoids repeating the same subquery. For a single comparison, go with the subquery. Once you need that value more than once, pull it into a CTE.
2. Personal best score (correlated subquery vs. pre-aggregated CTE)
This is called a correlated subquery because the inner query refers back to the outer query's row (qs.member_id in our example). That means it re-runs once for every row in the outer query, not just once overall.
This is the best case for when picking a CTE is the best option over a subquery. The correlated subquery reruns its max-score lookup for every single row which is barely noticeable on a small table such as ours but as the data sets get bigger the operation get considerably slower. The CTE calculates every member's best score in one pass with GROUP BY, then joins the results back thus one calculation is done instead of many.
Wrapping Up
Three clear signs it's time to switch from a subquery to a CTE: you've written a correlated subquery that reruns for every row (pre-aggregate it instead), you're repeating the same subquery more than once in a query (name it once instead), or your query has several logical steps you want to keep readable. Outside of those cases, a short, one-time subquery is often the simpler choice.
Top comments (0)